topic Re: Nested Ranking in QlikView

Nested Ranking

Tue, 16 Jun 2015 14:29:00 GMT

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

Re: Nested Ranking

Anonymous — Tue, 16 Jun 2015 16:03:56 GMT

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.

Re: Nested Ranking

sinanozdemir — Tue, 16 Jun 2015 17:28:00 GMT

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:

Re: Nested Ranking

Thu, 18 Jun 2015 07:30:54 GMT

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

Re: Nested Ranking

sinanozdemir — Thu, 18 Jun 2015 12:46:41 GMT

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.

Re: Nested Ranking

Kushal_Chawda — Thu, 18 Jun 2015 13:56:51 GMT

Please see the attached one

Re: Nested Ranking

Sat, 20 Jun 2015 12:11:06 GMT

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