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?
tell me please if I do not clearly describe my problem