topic Re: Rank with subtotals in QlikView

Rank with subtotals

Tue, 18 Aug 2015 19:20:57 GMT

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

Re: Rank with subtotals

Tue, 18 Aug 2015 20:05:38 GMT

Let try with Dimension Limits on straight table.

Re: Rank with subtotals

qlikviewwizard — Wed, 19 Aug 2015 01:55:24 GMT

Hi,

Please use this application.

Re: Rank with subtotals

Wed, 19 Aug 2015 05:35:55 GMT

gwassenaar‌ swuehl‌ MayilVahanan‌ sunindia‌ Please help

Re: Rank with subtotals

Wed, 19 Aug 2015 05:36:39 GMT

I am not getting sub total for Top 5. This I am already able to achieve

Re: Rank with subtotals

vikasmahajan — Wed, 19 Aug 2015 06:41:28 GMT

PFA solution

Vikas

Re: Rank with subtotals

sunny_talwar — Wed, 19 Aug 2015 06:41:45 GMT

May be not the best way to do it, but gets the result you are looking for:

Dimension: =ValueList($(vList))

Where vList

=Concat(DISTINCT {<Product = {"=Rank(Sum(Sales)) <= 5"}>} Chr(39) & Product & Chr(39), ', ') & ', ' &

Chr(39) & 'Subtotal Top5' & Chr(39) & ', ' & Chr(39) & 'Others' & Chr(39)

Expression:

=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)) = 'Subtotal Top5', Sum({<Product = {"=Rank(Sum(Sales)) <= 5"}>}Sales),

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

Re: Rank with subtotals

Wed, 19 Aug 2015 07:04:44 GMT

Thanks for the reply. But I think you have individually created expression using Value list.. In my case I have inputbox for Rank so now it's top 5 , but when user will input 10. it should be top 10.

Hope it is clear now

Re: Rank with subtotals

sunny_talwar — Wed, 19 Aug 2015 07:13:45 GMT

If there is a upper limit to the rank a user can enter, we can still make the above dynamic, but the expression will be huge. Not sure if there is another way to do it, may be somebody else can help you out.

Best,

Sunny

Re: Rank with subtotals

Kushal_Chawda — Wed, 19 Aug 2015 07:43:05 GMT

May be you can try something like below

Create inline Table

Measure:

LOAD * Inline [

Measure

3 ]

Create straight Table

Dimension:

=Pick(Measure,Product,'Sub Total Top $(vTop)','Others') where vTop is your variable

Expression:

=Pick(Measure,

sum({<Product={"=rank(sum(Sales),4)<=$(vTop)"}>}Sales),

sum({<Product={"=rank(sum(Sales),4)>$(vTop)"}>}Sales))

Also see some settings in Sort Tab of Chart and Text format of Dimension.

Hope it helps

See the attached.

Re: Rank with subtotals

Wed, 19 Aug 2015 07:50:38 GMT

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

Re: Rank with subtotals

Kushal_Chawda — Wed, 19 Aug 2015 07:58:45 GMT

Please see the attached

Re: Rank with subtotals

sunny_talwar — Wed, 19 Aug 2015 09:09:44 GMT

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

Re: Rank with subtotals

Wed, 19 Aug 2015 09:39:08 GMT

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.

Re: Rank with subtotals

sunny_talwar — Wed, 19 Aug 2015 09:42:43 GMT

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