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?

[haneeshmarella]

It's an official document hence cannot share the qvw

[vishsaggi]

Can you try like

IF($(=[Total Cartons]) >= $(=[Total Targets]), User_name)

[haneeshmarella]

Nope, gives me a '-' as output.

[vishsaggi]

Hmm not sure why. May be share your app with just that table like mask/scramble your data. Just follow these simple instructions to create a new qvw file with scramble data model.

Preparing examples for Upload - Reduction and Data Scrambling

[haneeshmarella]

Please find the sample.

[haneeshmarella]

Any solution Vishwarath? A tip for right answer isis that for Charla Hunter on week 7/16, OPERATORS AT RATE would be 8 for TOTAL OPERATORS of 9.

[sunny_talwar · Accepted solution]

Try this

Count(DISTINCT Aggr(

If(

Count(DISTINCT CASE#)/(Sum(Aggr(HOURS,USER_NAME,DATE,SUPERVISOR))*29) >= 1 or

Sum(UNITS#)/(Sum(Aggr(HOURS,USER_NAME,DATE,SUPERVISOR))*74) >= 1,

USER_NAME), SUPERVISOR, USER_NAME))

[haneeshmarella]

Thank you Sunny!!!!!!! Got it now, aggregate based on the condition at USERNAME level, and then aggregate (group by) at SUPERVISOR, USERNAME. These aggregations are so confusing!!

[sunny_talwar]

When you look at this whole expression... it is fairly complex, but not so much if you try to break it into pieces and build it together....

[haneeshmarella]

Absolutely. Thank you so much for the timely help again.