Creating calculated field column with true/false statement based on login

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!

The last calculation to calculate the percentage won’t work because from what I can see each training is a different row. So you need to use table calculations to calculate that over the whole dataset. This calculation should give the right percentage:

(

maxOver(
  {Robin Sort Eject},
  [employee_name],
  PRE_AGG
)

+

maxOver(
  {Robin Zone 1},
  [employee_name],
  PRE_AGG
)

+

maxOver(
  {Robin Zone 2},
  [employee_name],
  PRE_AGG
)


)
/
3

Regarding the second question, you could create a field along the following lines. Using sumOver it calculates the number of times the supervisor has assigned any of those two courses in the last 6 months using sum and then return 1 if that count is greater than 0. (I am assuming some field names since I could not deduce them from your screenshots… but should be easy change to what you have)

ifelse(
  sumOver(
    ifelse( 
      {Training Assigned on} > addDateTime(-6, 'MM', now())
      AND (training_title = 'ARU FAST Certification ' OR training_title = 'ARU UP Certification'),
      1,0
    ),
    [supervisor_name],
    PRE_AGG
  ) > 0,
  1, 0
)

(Also here I am partitioning using employee_name and supervisor_name… ideally you would partition by some unique id if you have such a field available because different people can have the same name)

1 Like