Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
In the pivot table, because I need to limit the Code according to the size of the cumulative value, I don’t know how to calculate the cumulative value in the dimension, so I use the value of the expression to limit the display value of the dimension Code. When the expression value is not null, the code displays, otherwise, it does not display.
the expression is:
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)
But now I want to add a a serial number to the pivot table dimension sort by Qty of the 6th week from largest to smallest, like the below:
But I use the formula: =aggr(rank(num(sum({<Week={"6"}>}Qty),'#,##0')),Code) it's result is the below:
I can’t limit the dimensions according to the cumulative value condition of the expression.
I tried formulas about rowno(), it doesn't work,too.
This is a simplified example, the data actually has many rows.
Does anyone have a good solution? It would be great if the rank function could not be used, because the use of the rank function must limit the conditions related to the cumulative value of the Code, currently I don’t know how to calculate the minimum value of the cumulative value of the week (Week>=4) in the dimension.
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.
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.