Problem with AGGR function

sparur — Fri, 05 Mar 2010 08:34:35 GMT

Hi, colleagues.

I have a problem with the AGGR function . See attached file. There are 2 pivot tables.

1) by Production Line. It's Ok.

2) by Technology. with bug in expression "Base capacity".

Here is an expression:

IF(numsum(sum({<Year=>} TRS * TOC * MLCS), sum({<Year=>} BC)) <> 0,
IF(sum({<Year=>} BC)=0, sum({<Year=>} TRS * TOC * MLCS / 1000), sum({<Year=>} BC)),
AGGR(rangesum(before([Capacity (kTn)], (ColumnNo()-(ceil(ColumnNo()/13)-1)*13), 13)) * CAPACITY, ProdLine, PeriodYear))

the problem is that the field of Capacity is set at the level of the production line(see in Data Model). And when we calculate by ProdLine, there is no problem (pivot 1). But when we want to see by technology(pivot 2), then the problem starts here ...

for example, select 'Filled Bar' in Technology filter and then select 2 Production Line: 'CHI FB1' ? 'CHI FB3'

We have Base Capacity (BC) for the 2009 year is 46,1 for CHI FB1 and 25,5 for CHI FB3. so fo Technology Filled Bar we have BC = 71,6 (see in pivot 3).

And now we have to calculate a BC for 'P1 10' (for example)...

Capacity for that period ( 'P1 10') in CHI FB1 = 0,4 (40%), in CHI FB3 = 0,0769 (7,69%) (see in pivot 4 - column with red background)

ProdLine BC Explanation

CHI FB1 18,44 (46,1 * 0,4)

CHI FB3 1,96 (25,5 * 0,0769)

so what I want :)...

I want that in pivot by Technology (pivot 2) for that period (P1 10) calculated Base Capacity = 20,4

How I must correct my expression (Base Capacity) in pivot 2 for calculate and see it?

Problem with AGGR function

sparur — Fri, 05 Mar 2010 10:01:16 GMT

anyone have any ideas?

Problem with AGGR function

sparur — Tue, 09 Mar 2010 07:29:48 GMT

I'm clear? tell me please if I do not clearly describe my problem

topic Problem with AGGR function in QlikView

Problem with AGGR function

Problem with AGGR function

Problem with AGGR function