Aggregate Calculation Dimension Help

[haneeshmarella]

I have the following columns

TOTAL CARTONS Count(distinct CASE#)

TOTAL PIECES num(sum(UNITS#),'#,##0')

TARGET CARTONS sum(aggr(HOURS,USER_NAME,DATE,SUPERVISOR))*29

TARGET PIECES sum(aggr(HOURS,USER_NAME,DATE,SUPERVISOR))*74

I am trying to COUNT DISTINCT the USER_NAME for each row in the column OPERATORS AT RATE based on condition that TOTAL CARTONS >= TARGET CARTONS something like this:

aggr(count(distinct IF(TOTAL CARTONS >= TARGET CARTONS, USER_NAME), DIMENSION....)

What dimension do I use here to get the right output of OPERATORS AT RATE for each SUPERVISOR per WEEK?

[CarlosAMonroy]

Hi Haneesh,

You can try the following:

if( TOTAL CARTONS >= TARGET CARTONS, sum(aggr(count(distinct USER_NAME),WEEK_START)) )

Hope that works,

Carlos M

[haneeshmarella]

Hi Carlos,

I don't get any output. I just get '-'.

[neelamsaroha157]

May be you can use i- if(column(2)>column(4), UserName), you might not aggregation function.

[haneeshmarella]

Already tried this Neelam, it did not work.

[neelamsaroha157]

Can you share a sample??

[CarlosAMonroy]

Would be useful if you share your example. Also check the field names match your data model.

Thanks,

Carlos

[vishsaggi]

Hope you are using square brackets around your label names like [Total Cartons] >= [Total Targets]

did you try like below:

= IF(Count(distinct CASE#) >= sum(aggr(HOURS,USER_NAME,DATE,SUPERVISOR))*29 , USER_NAME)

[haneeshmarella]

Yes, I did use [ ] and also did the above earlier but it did not work.

[haneeshmarella]

What do you want me to exactly share? It's an official document hence cannot share the qvw