Create different data blocks for range of years and show TOP "X" countries, "X" is dynamic

[Shruti_SK]

Hello Everyone!

Scenario:

I want to show the TOP "X" countries in the mentioned manner below in the bar chart:

• TOP 10 countries based on measure SUM(sales) for Years from 2000-2010 range
• TOP 5 countries based on measure SUM(sales) for Years from 2010-2020 range

The TOP "X" value and the YEARS range cannot be hardcoded and they need to be read from a code table. 

How to create such data blocks for various YEARS range which will be read from the table and the corresponding TOP "X" value to be read and calculated at the back end before I plot them on a bar chart?

NOTE: User will always select SINGLE YEAR value in the filter of the dashboard.

Kindly share your valuable suggestions. 

Thanks & Regards

Shruti

Qlik Sense Business  

[kaushiknsolanki]

If the user is going to select only one year, then its quite simple,

Create a Variable 'vTop' to calculate 'X', simply like this.

If(Year<=2010,10,5) 

Now create a Bar chart with Country as a dimension and enable the "Limitation"  and select "Fixed Number"

And put a variable name.

Now add expression as Sum(Sales) and in sorting keep expression as the first column.

This should solve your problem.

[Shruti_SK]

Hi Kaushik, 

Thanks for the response. 

But TOP "X" is also a variable. If the user decides to change TOP 10 to TOP 5 for 2000-2010 years, my code should understand this change and calculate TOP "X" countries for that year range. I cannot really use "Fixed Number" in the bar chart as the chart has to show TOP 10 sometimes, TOP 5 sometimes, TOP 15 sometimes. As a developer, I won't have control over TOP "X". 

Dynamic elements

1. Year Range
2. "X" in TOP X
3. Ranking of countries

vTOP Variable which should hold my ranks for the countries (which also has to be calculated dynamically at backend w.r.t Year range and "X" value)

Code Table 1:

Year Range	TOP X
2000 - 2010	10
2010 - 2020	15
2020 - 2023	5

Fact Table:

Year	Quarter	Sales	Country
2006	2006-Q1	1000	India
2000	2000-Q2	2000	Srilanka
2006	2006-Q2	3000	India
2006	2006-Q3	4500	USA
2006	2006-Q1	5000	UK
2006	2006-Q1	6000	Srilanka
2006	2006-Q1	7000	Maldives
2010	2010-Q2	8000	Qatar
2021	2021-Q1	9000	China
2021	2021-Q2	10000	Russia
2010	2010-Q2	23000	Malaysia
2010	2010-Q2	20000	Indonesia
2010	2010-Q3	21000	Cambodia
2021	2021-Q1	7000	India

My Results should be:

Year Selection	TOP "X"	Rank	Country
2006	5	1	Maldives
		2	Srilanka
		3	UK
		4	USA
		5	India
2010	3	1	Malaysia
		2	Cambodia
		3	Indonesia
2021	2	1	Russia
		2	China

Note: The country filter on the dashboard should also switch to these TOP "X" countries dynamically. 

How can I achieve this from the backend? Kindly respond.

Thanks & Regards

Shruti

[kaushiknsolanki]

Hi Find the solution attached here.

Hope this is what you wanted.

[Shruti_SK]

Hi Kaushik, 

Unfortunately, this is not the solution I am looking for. Because user can select only ONE YEAR in the dashboard. User will not have Range to be selected and the TOP "X" value will not be known to the user, this is something should be controlled at the backend. 

My expected result should look like this: 

For YEAR 2006 - TOP 5 Countries

For the Year 2010 - TOP 3 Countries should be visible - but my chart below shows 4 as fact table has 4 rows of data, but I need to calculate TOP 3 at backend using the same table and plot on the same chart as above. 

The single chart should show different results for different YEAR selection based on condition provided in CODE TABLE at the backend which has to be connected to my FACT TABLE where my RANKING should occur dynamically. 

Thanks & Regards

Shruti