Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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.***
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.****
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.
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.***
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.
Yes you can add number of dimensions in that.