Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

sparur
Valued Contributor II

Problem with AGGR function

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

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?

Tags (1)
2 Replies
sparur
Valued Contributor II

Problem with AGGR function

anyone have any ideas?

sparur
Valued Contributor II

Problem with AGGR function

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

Community Browser