How to filter cumulative values in pivot table

[Nora104]

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?

[edwin · Accepted solution]

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

[Nora104]

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!

[Nora104]

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)

[edwin]

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.

[Nora104 · Accepted solution]

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.