Relative numbers based on dimension limitation

[poklegoguy]

Hi, I am trying to create a table to display the values of the top 20 selling items and I have a measure that is going to calculate the sales as share per total or sales ratio, let's say I have a total of 30 items and I only want to show the statistics for the top 20 items, I've set a limitation of 20 for my dimension and set the measure modifier to 'Relative Numbers', I've also tried this expression 'SUM(Sales) / SUM(TOTAL Sales)'. However, I realized that the returned values are in relative to all the 30 items instead of the 20 items that are limited in the dimension. 

Below is an example of what I need:
Exp Sales Table:
Product | Sales
A | 30
B | 10
C | 25
D | 25
E | 10

Let say I want the stats for the top 3 selling product, it should have figures as table below:
Product | Sales | Sales%
A | 30 | 30/(30+25+25) = 37.5%
C | 25 | 25/(30+25+25) = 31.25%
D | 25 | 25/(30+25+25) = 31.25%

But instead, I am getting figures as below:
Product | Sales | Sales%
A | 30 | 30/(30+25+25+10+10) = 30.00%
C | 25 | 25/(30+25+25+10+10) = 25.00%
D | 25 | 25/(30+25+25+10+10) = 25.00%

How can I get the relative value of the limited 20 items or is there a workaround for this? Any help is appreciated!

[TauseefKhan]

Hi @poklegoguy,

Use below approach to get desired result :

https://community.qlik.com/t5/App-Development/Row-level-calculation-in-a-table/m-p/2456051#M98483

you need to create variable for top 3 selling product.


******Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.****

[poklegoguy]

Hi @TauseefKhan, thanks for the reply. However, I can't seems to understand how to use the approach you suggested me for my need. It doesn't seem similar. 

[TauseefKhan · Accepted solution]

Hi @poklegoguy 

1- Dimension: Product

2- Measures:

Top 3 Sales Sum:

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

Sales% Calculation:

=Sum(Sales) / Sum({<Product={"=Rank(Sum(Sales))<=3"}>} Total Sales) * 100

or

you can apply Dimension Limitation:

In the dimension settings, limit it to the top 3/20 products.

***Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.***

[poklegoguy]

Hi @TauseefKhan, it doesn't seem to work on my end. Can your solution work with more than 1 dimension? What if I have multiple dimensions like product code and product name? I tried setting the dimension limitation to 20 according to my need but it returns me the sales% figure divided by all the possible count of product in the dimension, omitting the limitation set.

[TauseefKhan]

Yes you can add number of dimensions in that.