topic Re: MAT Total + Top N in QlikView

MAT Total + Top N

ninnartx — Sun, 06 May 2018 03:22:33 GMT

Hi everyone,

I have a table showing MAT sales of my Top 3 products on the left. I would like to add another column - total sales - to show the total sales in that period regardless of product (but only for my top 3 products). However, when I added TOTAL to my formula, it disregards my set analysis for Top 3 products and "Product B" appeared, as seen on table on the right. How do I get rid of the highlighted rows?

I've attached a sample app. MAT has to be calculated on the frontend because I would like the period to be flexible + users may make other selections. Any help would be greatly appreciated!!!

Re: MAT Total + Top N

Neymar_Jr — Sun, 06 May 2018 04:14:55 GMT

Hi,

See if this works for you.

Thanks,

Re: MAT Total + Top N

ninnartx — Sun, 06 May 2018 09:41:14 GMT

Yes it did, thank you!

Just out of curiosity, since the IF command affects performance, do you think there's any other way to achieve the same result without sacrificing performance?

Re: MAT Total + Top N

vinieme12 — Sun, 06 May 2018 10:23:58 GMT

You just need to restrict your dimensions by placing the Rank condition of Top 3 outside your Pick Match

=if(AGGR(Rank(Sum(Value))<=3,_RollingMAT,Product)

,Pick(_RollingMAT,

sum(TOTAL <Yr_Mth> {<Yr_Mth={">=$(=addmonths(max(Yr_Mth),-23))<=$(=addmonths(max(Yr_Mth),-12))"},

Product={"=Rank(Sum(Value))<=3"}>}Value),

sum(TOTAL {<Yr_Mth={">=$(=addmonths(max(Yr_Mth),-20))<=$(=addmonths(max(Yr_Mth),-9))"},

Product={"=Rank(Sum(Value))<=3"}>}Value),

sum(TOTAL {<Yr_Mth={">=$(=addmonths(max(Yr_Mth),-17))<=$(=addmonths(max(Yr_Mth),-6))"},

Product={"=Rank(Sum(Value))<=3"}>}Value),

sum(TOTAL {<Yr_Mth={">=$(=addmonths(max(Yr_Mth),-14))<=$(=addmonths(max(Yr_Mth),-3))"},

Product={"=Rank(Sum(Value))<=3"}>}Value),

sum(TOTAL {<Yr_Mth={">=$(=addmonths(max(Yr_Mth),-11))<=$(=addmonths(max(Yr_Mth),0))"},

Product={"=Rank(Sum(Value))<=3"}>}Value))

)

Re: MAT Total + Top N

vinieme12 — Sun, 06 May 2018 10:28:18 GMT

See attached app, use your existing expression with Calculated dimension, so you won't have to add the Rank restriction condition to each of your expressions