Aggregation help?

[haneeshmarella]

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?

[vishsaggi]

Can you share a sample to look into? And also what is the error you are getting?

[rwunderlich]

Could you explain in words what the logic is?

-Rob

[haneeshmarella]

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?