pivot formula does not work

[markusblaut]

Hi people,

I have calculated an KPI with the following formula:

(rangesum(above(sum([IstAEzuFP 2018]),0,RowNo())))

/

Sum(Aggr(If(Sum([IstAEzuFP 2018]) > 0, Sum([BudgetzuFP 2018])),AccountingMonth))

So the formula perfectly works in the KPI.

But if I put the formula in an Pivot table to distinguish between products it does not work at all:

Does anyone has an idea how I can make the formula work also in the pivot!?

Tank you!

[marcus_sommer]

I think you need to add your other pivot-dimensions to the aggr-function so that they are also regarded within the calculation.

- Marcus

[markusblaut]

Could you give me an example?

Tank you!!!

[marcus_sommer]

Maybe in this way:

...

Sum(Aggr(If(Sum([IstAEzuFP 2018]) > 0, Sum([BudgetzuFP 2018])),

     AccountingMonth, System, [Type(Vol)], ProfitCenter))

- Marcus

[markusblaut]

Hi Marcus,

it is "better" now. I Added the dimension "system" in the aggr function an I got values (see screenshot). But those values are not correct yet.

If I do an filter on the System (SIPROTEC for example), the values are changing to the correct numbers!

But I would like to see the correct numbers already in the total pivot overview.

Do you have any idea why that is not working?

Thank you so much!

[marcus_sommer]

It's difficult to say what didn't work. Maybe it's not enough to add System within the aggr() else it might be the wrong field or there are additionally dimensions needed. Depending on your datamodel it might be also necessary to use a NODISTINCT statement within the aggr() or a TOTAL statement within the outer aggregation or similar.

For testing I would further split the two expression parts - rangesum(abvove()) und aggr() - to be sure that each part returned the expected values.

- Marcus

[markusblaut]

Thank you! Just found out, that the aggr. function is working proberly!

The problem is the rangesum part of the formula.

As soon as I select a System (e.g. SIPROTEC) in the Pivot the amount of the rangesum changes to the correct value, before selecting a system the rangesum shows a wrong value. (see screenshot)

Thank you for any help!