6 Replies Latest reply: May 23, 2018 7:56 AM by Markus Blaut

# pivot formula does not work

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!

• ###### Re: pivot formula does not work

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

- Marcus

• ###### Re: pivot formula does not work

Could you give me an example?

Tank you!!!

• ###### Re: pivot formula does not work

Maybe in this way:

...

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

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

- Marcus

• ###### Re: pivot formula does not work

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!

• ###### Re: pivot formula does not work

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

• ###### Re: pivot formula does not work

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!