Nested Ranking

[#1]

Hi,

I am new to Qlikview. I would like to do Nested ranking.

Need to first Rank Top 5 Countries. In this top 5 country, for each country need to display top 2 states.

Sample data we have is:

Country State Sales

India     TN     1000

India      KN     700

India      KL     1000

UAE     Dubai    900

UAE     AbuDhabi 800

UAE      ABC     700

US     S1     1000

US     S2     2000

US     S3 3000

Gave a try with Dimension limits, set analysis Rank function...Was able to travel still one ranking but could not get nested ranking...

Thanks in advance for the help.

Regards,

Qlikshare

[Anonymous]

Hi,

wellcome!

Try with this, I work with Dimension limit with this formula:

=if(COUNT(DISTINCT Country)<2,2,3)

(3 Country and 2 state, you can change this).

If you click over a country in the graph you see the two states of the selected country that have the Greater sales.

Regards.

[sinanozdemir]

You can accomplish this by adding a button to select top 2 states within top 5 countries:

When you click on the button, it selects the top 2 states within top 5 countries:

[#4]

Thanks Sinan. Is it possible to do same without Button.

The table has to be displayed always top 5 country and for each of this top 5 country show top 2 state.

Thanks,

qlikshare

[sinanozdemir · Accepted solution]

Sure, please see the below screenshot:

I placed the Aggr() function in an If statement and set it to <= 2. When this is true, I summed the sales by country.

Hope this helps.

[Kushal_Chawda]

Please see the attached one

[#7]

Many Thanks Sinan and Kush..Both of your solutions worked.