Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I need the count distinct of USER_NAME which meet an IF condition per SUPERVISOR per week.
The fields used are USER_NAME, SUPERVISOR, DATE (MM/DD/YYYY).
The condition is
IF(
IF(Count(distinct
IF(OFF_ON_LINE='OFF-LINE',CASE#))=0,'-',Count(distinct IF(OFF_ON_LINE='OFF-LINE',CASE#)))
/
(sum(IF(OFF_ON_LINE='OFF-LINE', aggr(IF(OFF_ON_LINE='OFF-LINE',HOURS),SUPERVISOR,OFF_ON_LINE,DATE,USER_NAME)))*70) >= 1,
USER_NAME)
The expression I use is
Count(DISTINCT Aggr(If(
IF(Count(distinct IF(OFF_ON_LINE='OFF-LINE',CASE#))=0,'-',Count(distinct IF(OFF_ON_LINE='OFF-LINE',CASE#)))/(sum(IF(OFF_ON_LINE='OFF-LINE', aggr(IF(OFF_ON_LINE='OFF-LINE',HOURS),SUPERVISOR,OFF_ON_LINE,DATE,USER_NAME)))*70) >= 1,
USER_NAME), SUPERVISOR, DATE))
What am I doing wrong?
Can you share a sample to look into? And also what is the error you are getting?
Could you explain in words what the logic is?
-Rob
The logic is
If the number of cartons picked divided by target cartons (Hours*70) >=1, then that User meets the target. I am trying to calculate the users count distinct based on that condition, per week per supervisor.
Does this help, Rob?