Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ninnartx
Creator
Creator

MAT Total + Top N

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!!!

1 Solution

Accepted Solutions
vinieme12
Champion II
Champion II

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))

)

View solution in original post

4 Replies
Neymar_Jr
Creator II
Creator II

Hi,

See if this works for you.

Thanks,

RT

ninnartx
Creator
Creator
Author

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?

vinieme12
Champion II
Champion II

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))

)

View solution in original post

vinieme12
Champion II
Champion II

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