Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
See why BI users voted Qlik #1 in 11 categories. GET REPORT
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
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
Highlighted
MVP & Luminary
MVP & Luminary

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

- Marcus

Highlighted
Contributor III
Contributor III

Could you give me an example?

Tank you!!!

Highlighted
MVP & Luminary
MVP & Luminary

Maybe in this way:

...

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

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

- Marcus

Highlighted
Contributor III
Contributor III

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!

Highlighted
MVP & Luminary
MVP & Luminary

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

Highlighted
Contributor III
Contributor III

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!