topic Re: Aggregate Calculation Dimension Help in QlikView

Aggregate Calculation Dimension Help

haneeshmarella — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Aggregate Calculation Dimension Help

CarlosAMonroy — Thu, 26 Jul 2018 20:41:03 GMT

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

Re: Aggregate Calculation Dimension Help

haneeshmarella — Thu, 26 Jul 2018 20:44:01 GMT

Hi Carlos,

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

Re: Aggregate Calculation Dimension Help

neelamsaroha157 — Thu, 26 Jul 2018 20:46:05 GMT

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

Re: Aggregate Calculation Dimension Help

haneeshmarella — Thu, 26 Jul 2018 20:47:49 GMT

Already tried this Neelam, it did not work.

Re: Aggregate Calculation Dimension Help

neelamsaroha157 — Thu, 26 Jul 2018 20:48:21 GMT

Can you share a sample??

Re: Aggregate Calculation Dimension Help

CarlosAMonroy — Thu, 26 Jul 2018 20:58:01 GMT

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

Thanks,

Carlos

Re: Aggregate Calculation Dimension Help

vishsaggi — Thu, 26 Jul 2018 21:09:48 GMT

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)

Re: Aggregate Calculation Dimension Help

haneeshmarella — Thu, 26 Jul 2018 21:18:35 GMT

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

Re: Aggregate Calculation Dimension Help

haneeshmarella — Thu, 26 Jul 2018 21:19:25 GMT

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

Re: Aggregate Calculation Dimension Help

haneeshmarella — Thu, 26 Jul 2018 21:20:01 GMT

It's an official document hence cannot share the qvw

Re: Aggregate Calculation Dimension Help

vishsaggi — Thu, 26 Jul 2018 21:23:30 GMT

Can you try like

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

Re: Aggregate Calculation Dimension Help

haneeshmarella — Thu, 26 Jul 2018 21:26:47 GMT

Nope, gives me a '-' as output.

Re: Aggregate Calculation Dimension Help

vishsaggi — Thu, 26 Jul 2018 21:33:23 GMT

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

Re: Aggregate Calculation Dimension Help

haneeshmarella — Thu, 26 Jul 2018 21:41:14 GMT

Please find the sample.

Re: Aggregate Calculation Dimension Help

haneeshmarella — Fri, 27 Jul 2018 13:44:38 GMT

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.

Re: Aggregate Calculation Dimension Help

sunny_talwar — Fri, 27 Jul 2018 14:02:53 GMT

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))

Re: Aggregate Calculation Dimension Help

haneeshmarella — Fri, 27 Jul 2018 14:09:20 GMT

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!!

Re: Aggregate Calculation Dimension Help

sunny_talwar — Fri, 27 Jul 2018 14:16:46 GMT

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....

Re: Aggregate Calculation Dimension Help

haneeshmarella — Fri, 27 Jul 2018 14:18:58 GMT

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