# 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