Rank with subtotals

[#1]

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

[#2]

Let try with Dimension Limits on straight table.

[qlikviewwizard]

Hi,

Please use this application.

[#4]

gwassenaar‌ swuehl‌ MayilVahanan‌ sunindia‌  Please help

[#5]

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

[vikasmahajan]

PFA  solution

Vikas

[sunny_talwar]

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

[#8]

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

[sunny_talwar]

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

[Kushal_Chawda]

May be you can try something like below

Create inline Table

Measure:

LOAD * Inline [

Measure

1

2

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

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.