Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

guy_benhaim
New Contributor

Conditional Summation of Pivot Rows

Hello,

I have a pivot table with 3 dimensions, in which I would like to sum the "Dist" expression only for the rows that have "1" in their "load" expression in the row below. I did not manage to do it properly, such that if I collapse summation works.

        Aggr ... , TOTAL ..., Below() ....

The lowest dimension is sorted, and "below" is determined by sorting.

Please advise.

temp.PNG

Best

5 Replies
jeshwanth19
Contributor II

Re: Conditional Summation of Pivot Rows


Below Expression might work


Sum(If(Load(Pre)=1, Dist))

guy_benhaim
New Contributor

Re: Conditional Summation of Pivot Rows

Does not recognize the operator "Pre".

Did you test this?

jeshwanth19
Contributor II

Re: Conditional Summation of Pivot Rows

As per your screenshot, i considered it to be a field in your model.

guy_benhaim
New Contributor

Re: Conditional Summation of Pivot Rows

Ah ... that's the label. The field is "Load".

But, the condition should be that Load=1 in the row below the row whose "Dist" we are summing.

guy_benhaim
New Contributor

Re: Conditional Summation of Pivot Rows

This works, per line:  If(Below(Load)=1 or Below(Load)=0,Sum(Dist),0)

But when I collapse the dimension it sums to 0 ...