Solved: Re: Rank with subtotals - Page 2 - Qlik Community

Report Inappropriate Content · ‎2015-08-18

Hi All,

I am facing the issue in creating Straight table with Rank. I have products and in front end I am having input box to show ranks dynamically. I am able to achieve the dynamic ranking , but I am not able to achieve below format

Lets say for Top 5 Product I want to achieve below format

Product Sales

1 50

2 40

3 30

4 20

5 10

Sub Total Top 5 150

Others 100

Grand Total 250

I tried using value list but it's not working as Top Product is dynamic.

Please suggest it's urgent

Report Inappropriate Content · ‎2015-08-19

Hi kush,

Thanks a lot for this. This is very close to what I need. But only one prob. , Grand Total is wrong. Can you help me to correct it

Kushal_Chawda · ‎2015-08-19

Please see the attached

sunny_talwar · ‎2015-08-19

This is little more dynamic (I have added the expression for up to 25 rank) you can add more if the user enter more then 25:

=If(Dimensionality() = 0, Sum(Aggr(Avg(Sales), Product)),

If(ValueList($(vList)) = Only({<Product = {"=Rank(Sum(Sales)) = 1"}>}Product), Sum({<Product = {"=Rank(Sum(Sales)) = 1"}>}Sales),

If(ValueList($(vList)) = Only({<Product = {"=Rank(Sum(Sales)) = 2"}>}Product), Sum({<Product = {"=Rank(Sum(Sales)) = 2"}>}Sales),

If(ValueList($(vList)) = Only({<Product = {"=Rank(Sum(Sales)) = 3"}>}Product), Sum({<Product = {"=Rank(Sum(Sales)) = 3"}>}Sales),

If(ValueList($(vList)) = Only({<Product = {"=Rank(Sum(Sales)) = 4"}>}Product), Sum({<Product = {"=Rank(Sum(Sales)) = 4"}>}Sales),

If(ValueList($(vList)) = Only({<Product = {"=Rank(Sum(Sales)) = 5"}>}Product), Sum({<Product = {"=Rank(Sum(Sales)) = 5"}>}Sales),

If(ValueList($(vList)) = Only({<Product = {"=Rank(Sum(Sales)) = 6"}>}Product), Sum({<Product = {"=Rank(Sum(Sales)) = 6"}>}Sales),

If(ValueList($(vList)) = Only({<Product = {"=Rank(Sum(Sales)) = 7"}>}Product), Sum({<Product = {"=Rank(Sum(Sales)) = 7"}>}Sales),

If(ValueList($(vList)) = Only({<Product = {"=Rank(Sum(Sales)) = 8"}>}Product), Sum({<Product = {"=Rank(Sum(Sales)) = 8"}>}Sales),

If(ValueList($(vList)) = Only({<Product = {"=Rank(Sum(Sales)) = 9"}>}Product), Sum({<Product = {"=Rank(Sum(Sales)) = 9"}>}Sales),

If(ValueList($(vList)) = Only({<Product = {"=Rank(Sum(Sales)) = 10"}>}Product), Sum({<Product = {"=Rank(Sum(Sales)) = 10"}>}Sales),

If(ValueList($(vList)) = Only({<Product = {"=Rank(Sum(Sales)) = 11"}>}Product), Sum({<Product = {"=Rank(Sum(Sales)) = 11"}>}Sales),

If(ValueList($(vList)) = Only({<Product = {"=Rank(Sum(Sales)) = 12"}>}Product), Sum({<Product = {"=Rank(Sum(Sales)) = 12"}>}Sales),

If(ValueList($(vList)) = Only({<Product = {"=Rank(Sum(Sales)) = 13"}>}Product), Sum({<Product = {"=Rank(Sum(Sales)) = 13"}>}Sales),

If(ValueList($(vList)) = Only({<Product = {"=Rank(Sum(Sales)) = 14"}>}Product), Sum({<Product = {"=Rank(Sum(Sales)) = 14"}>}Sales),

If(ValueList($(vList)) = Only({<Product = {"=Rank(Sum(Sales)) = 15"}>}Product), Sum({<Product = {"=Rank(Sum(Sales)) = 15"}>}Sales),

If(ValueList($(vList)) = Only({<Product = {"=Rank(Sum(Sales)) = 16"}>}Product), Sum({<Product = {"=Rank(Sum(Sales)) = 16"}>}Sales),

If(ValueList($(vList)) = Only({<Product = {"=Rank(Sum(Sales)) = 17"}>}Product), Sum({<Product = {"=Rank(Sum(Sales)) = 17"}>}Sales),

If(ValueList($(vList)) = Only({<Product = {"=Rank(Sum(Sales)) = 18"}>}Product), Sum({<Product = {"=Rank(Sum(Sales)) = 18"}>}Sales),

If(ValueList($(vList)) = Only({<Product = {"=Rank(Sum(Sales)) = 19"}>}Product), Sum({<Product = {"=Rank(Sum(Sales)) = 19"}>}Sales),

If(ValueList($(vList)) = Only({<Product = {"=Rank(Sum(Sales)) = 20"}>}Product), Sum({<Product = {"=Rank(Sum(Sales)) = 20"}>}Sales),

If(ValueList($(vList)) = Only({<Product = {"=Rank(Sum(Sales)) = 21"}>}Product), Sum({<Product = {"=Rank(Sum(Sales)) = 21"}>}Sales),

If(ValueList($(vList)) = Only({<Product = {"=Rank(Sum(Sales)) = 22"}>}Product), Sum({<Product = {"=Rank(Sum(Sales)) = 22"}>}Sales),

If(ValueList($(vList)) = Only({<Product = {"=Rank(Sum(Sales)) = 23"}>}Product), Sum({<Product = {"=Rank(Sum(Sales)) = 23"}>}Sales),

If(ValueList($(vList)) = Only({<Product = {"=Rank(Sum(Sales)) = 24"}>}Product), Sum({<Product = {"=Rank(Sum(Sales)) = 24"}>}Sales),

If(ValueList($(vList)) = Only({<Product = {"=Rank(Sum(Sales)) = 25"}>}Product), Sum({<Product = {"=Rank(Sum(Sales)) = 25"}>}Sales),

If(ValueList($(vList)) = 'Subtotal Top ' & vRank, Sum({<Product = {"=Rank(Sum(Sales)) <= vRank"}>}Sales),

If(ValueList($(vList)) = 'Others', Sum({<Product = {"=Rank(Sum(Sales)) > vRank"}>}Sales)))))))))))))))))))))))))))))

Output when user enter 5:

Output when user enters 20:

I cannot see Kush's solution which might be better, but just giving you another solution to give you food for thought

HTH

Best,

Sunny

Report Inappropriate Content · ‎2015-08-19

Hi sunny,

User will enter only till 15.. So need to create the 15 expression. I think it will be more complex. But I appreciate for giving your time.

sunny_talwar · ‎2015-08-19

You need just one expression. For 15 you will need this one big expression: