topic Re: Aggregate a third time with AGGR? in QlikView

Aggregate a third time with AGGR?

Wed, 29 Jan 2014 18:58:36 GMT

Hi all,

I have the following expression that I'm trying to get to calculate correctly over a Summary Level cyclic group (Summary Level 4, Summary Level 1, Cost Element, highest to lowest) in a stacked bar chart. I can get it to show what look like the correct numbers for Cost Element, but it won't do it for the higher level values - Summary Level 4 and Summary Level 1. Below is what I have:

=sum(aggr(
sum(DOLLAR_COST) /
if(COSTELEMENT_LIST = 'Product BOM Cost (excl Install kits & UPM)' or COSTELEMENT_LIST = 'Install Kits' or COSTELEMENT_LIST = 'Freight & Duty', sum(TOTAL_KW_NEW_BUILD), sum(TOTAL_KW_BUILD ) )
, $(=GetCurrentField(SummaryLevelCyclic)), QTR
))

The BUILD numbers are broken out by month and by cost element in the data model. I think I somehow need to aggregate the build numbers to levels higher than cost element in the denominator, but I can't add another SUM() there, and moving the placement of the SUM() within the denominator doesn't seem to work. Does anyone have any ideas?

Thanks!

Robin

Re: Aggregate a third time with AGGR?

greg-anderson — Wed, 29 Jan 2014 19:04:16 GMT

Robin, is this a document you can post for review? It looks like your AGGR statement might just need some formatting, but it is hard to tell without data to test.

Based on the parentheses as placed, I see only two AGGR levels, $(=GetCurrentField(SummaryLevelCyclic)) and QTR).

Try adding your third level after QTR (e.g., "...QTR, next level).

Re: Aggregate a third time with AGGR?

iktrayanov — Wed, 29 Jan 2014 19:14:48 GMT

Try this

Re: Aggregate a third time with AGGR?

Wed, 29 Jan 2014 19:43:05 GMT

Issue was resolved with this:

=sum(aggr(

sum(DOLLAR_COST) /

if (COSTELEMENT_LIST='Product BOM Cost (excl Install kits & UPM)' or COSTELEMENT_LIST = 'Install Kits' or COSTELEMENT_LIST = 'Freight & Duty',sum(TOTAL_KW_NEW_BUILD),sum(TOTAL_KW_BUILD)),

PERIOD_QTR, SUMMARIZE_LEVEL1, SUMMARIZE_LEVEL4, COSTELEMENT_LIST

))