Calculate proportion of fields that are in DONE status based on unique conditions that define DONE

[patrick_d]

Hi, after a fair bit of forum searching I am reaching out for assistance.

The scenario context is for screening volunteers, where a key part of the process requires 6 items to be done as tracked in Dynamics 365.

These items may be done in different orders ie. Not sequential.

The definition of done is different for each item/field ie.

 vnpc_status eq 100000003

 wwcc_status eq 100000001        

 ref1_date eq any date prior to today

 ref2_date eq any date prior to today

 interview_date eq any date prior to today

 training_date eq any date (can be in the future)

The need is the ability to select records according to how many of these steps have been complete (given an elapsed period of time -  but I am OK with that part).

Excel table below provides sample data and fields showing in yellow the " # Items Complete of 6"  column that I would like your help to create.

Note how for most, it is just a case of BLANK/NOT BLANK (so a simple solution based on this would capture 90% of the need) but anonymous4 has an entry under vnpc_status which is not correct for it to be complete AND other than training_date all dates should be historical.

Many thanks for your assistance - much appreciated.

[HotChilli]

Hi,

There's no Excel attached as far as i can see.  Sample data and desired outcome and someone will help you.

[patrick_d · Accepted solution]

Sorry, I am not sure why my image upload does not show.

On reflection, I realised that a simple approach can be taken to achieve this. At this point, I have just done a simplified version that checks for ISBLANK only (rather than verifying against the specfic criteria as listed above).

new_screening_progress =
IF((ISBLANK(contacts[new_wwccstatus])),0,1)
+ IF((ISBLANK(contacts[new_vnpcstatus])),0,1)
+ IF((ISBLANK(contacts[new_datecompletedref1])),0,1)
+ IF((ISBLANK(contacts[new_interviewdate])),0,1)
+ IF((ISBLANK(contacts[new_datecompletedref2])),0,1)
+ IF((ISBLANK(contacts[new_pendingtrainingdate])),0,1)

This then provides me with what I need ie. a column that provides the number of DONE items regardless of which ones they are.

As this works, I assume I should be able to modify by finding the right expression to use for each IF statement instead of ISBLANK ?

[Anonymous]

Why don't you simply add all columns and at the end just check result is blank or not..if it is blank then zero else result.

Thanks
Pravin


If it resolves your problem mark it as a solution and give Kudos.