Hi everyone, On Quicksight, I am trying to create a calculated field that checks whether the login has completed 3 modules from knet and give a 1 if they have all 3, .33 if only 1 and .66 if only 2
I have tried using the formula used in the following question Aggregated and non aggregated
With the following formula used per module - ifelse(module_id = 123456 (no single-quotes needed unless it is a string) AND completed_date_utc >= day_1_date AND isNotNull(completed_date_utc), 1 , 0 )
Here are the exact formulas i am using
Robin Sort Eject- ifelse(
{training_title} = âARU Sort Eject Chute Safety Trainingâ
AND isNotNull({transcript_completion_date}),
1,
0
)
Robin Zone 1-
ifelse(
{training_title} = âARU ROBIN Zone 1 Safe Access Certificationâ
AND isNotNull({transcript_completion_date}),
1,
0
)
Robin Zone 2-
ifelse(
{training_title} = âARU ROBIN Zone 2 Safe Access Certificationâ
AND isNotNull({transcript_completion_date}),
1,
0
)
And finally, a calculated field with the purpose of summing all three up to verify whether a user has all 3 -
({ROBIN Zone 1}+{Robin Zone 2}+{Robin Sort Eject})/3
This however is not giving me the desired output per login as they are only taking into account a single module rather than all together as seen in the image below
Along with this field, I am also trying to implement the following calculated field column as well-
A column that shows whether or not the (Master) Trainer is active by verifying if they have assigned curriculum in last 6 months (determined by the latest date that anyone has been assigned an ARU FAST Certification or ARU UP Certification from the trainer, cross-referenced by name or employee ID)
Any help or resources that I can be referred to will be greatly appreciated!