topic Re: How to filter cumulative values in pivot table in QlikView

How to filter cumulative values in pivot table

Nora104 — Wed, 16 Dec 2020 07:45:11 GMT

I use the formula in pivot table: rangesum(before(Sum(Qty),0,ColumnNo()))

I want it to be like the below, only show value when Week>=4 and the min cumulative value<150:

But I use the formula:if(Week>=4 and rangesum(before(Sum(Qty),0,ColumnNo()))<150,rangesum(before(Sum(Qty),0,ColumnNo())),)

the Code 90811 Week6&Week7 value does not show.

I use the formula:

if(Week>=4
and min(aggr(rangesum(before(Sum(Qty),0,ColumnNo())),Week,Code))<150,
rangesum(before(Sum(Qty),0,ColumnNo()))
,)

the Code 90810 was showed...

How can I write the formula?

Re: How to filter cumulative values in pivot table

edwin — Wed, 16 Dec 2020 13:53:28 GMT

one simple way to address this is by script, build a bridge for your week that implements the logic for accumulation, week2 is associated to your fact weeks 1 and 2, Week 3 to 1,2,and 3.

so when you sum by the new Week field, each is already accumulated. so in your expression you just use
sum({<Week={">=4"}>}Qty)

see attached

Re: How to filter cumulative values in pivot table

Nora104 — Thu, 17 Dec 2020 06:25:11 GMT

I have simplified the actual problem. The actual operation will involve other data. If I use a script to add a new week field, it is difficult to calculate with other data. I am not familiar with writing script calculations and cannot use it freely. Anyway, this is a new idea.Thank you!

Re: How to filter cumulative values in pivot table

Nora104 — Thu, 17 Dec 2020 09:59:24 GMT

It seems that I have tried it successfully, so happy!

I use the formula:

if(Week>=4 and MIN(total<Code>{<Week={">=4"}>} AGGR(sum(aggr(rangesum(above(Sum(Qty),0,rowNo())),Code,Week)),Code,Week))<150,
sum(aggr(rangesum(above(Sum(Qty),0,rowNo())),Code,Week)),0)

Re: How to filter cumulative values in pivot table

edwin — Thu, 17 Dec 2020 11:51:50 GMT

what i was suggesting was to move the logic of your expression to the script. use of IF and AGGR in a chart will be very slow and that will show once your data grows. what i am saying becomes obvious when you look at the expressions side by side:

sum({<Week={">=4"}>}Qty)

if(Week>=4 and MIN(total<Code>{<Week={">=4"}>} AGGR(sum(aggr(rangesum(above(Sum(Qty),0,rowNo())),Code,Week)),Code,Week))<150,
sum(aggr(rangesum(above(Sum(Qty),0,rowNo())),Code,Week)),0)

having said that im glad you got something out of what i suggested.

Re: How to filter cumulative values in pivot table

Nora104 — Fri, 18 Dec 2020 02:29:55 GMT

I found that using this formula can calculate the cumulative value in the dimension：

MIN(total<Code>{<Week={">=4"}>} aggr(rangesum(above(sum(Qty),0,rowNo())),Code,Week))

It solved my problem.