Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all, I have a problem with grand total after impelementing top N in a pivot table, which has Dim1, Dim2, Year . see attached qvw.
I did top 3 Dim2, and the rest is made null() and suppress the null().
The formula for Dim2 is: =if(aggr(rank(sum(Budget) ),Dim1,Dim2 )<=3,Dim2,null())
The problem is when I select A, value of total 2012 is changed.
I tried to use “Others” instead of null() and unselect “suppress null value”.
The formula for Dim2 is: =if(aggr(rank(sum(Budget) ),Dim1,Dim2 )<=3,Dim2,’others’)
when I select A, value of total 2012 is kept as before.
I am puzzled by this.
Meanwhile, if Dim2 is a cyclic Dimension. After using the aggr function, the cyclic ability is lost too.
I appreciate your help.
Thanks for reply, mohit. My issue is different from your example. Could you take a look at my qvw and help me fix the total in yellow background? Thanks.