Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

markusblaut
New Contributor III

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!

6 Replies

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

markusblaut
New Contributor III

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

markusblaut
New Contributor III

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

markusblaut
New Contributor III

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!