topic Re: Joining Aggr and Rank to Count Distinct in QlikView

Joining Aggr and Rank to Count Distinct

tinkerz1 — Thu, 25 Feb 2016 13:21:31 GMT

Hi,

I am trying to find a formula that can rank a list of countries then perform a distinct count on a field for each date

My table is as follows:

1. How can the formula =Aggr(Rank(Country),Country) be poplulate to the bottom of the page?

2. How after ranking by country and date can a count distinct on ID be performed?

Thanks.

Date	Country	=if(Aggr(Rank(Country),Country,date)=2,Country)	=Aggr(Rank(Country),Country)	=count(distinct ID)
		-	-	36
01/07/2015	Brazil	-	9	1
01/07/2015	Canada	-	8	1
01/07/2015	China	-	7	1
01/07/2015	France	-	6	1
01/07/2015	Germany	-	5	1
01/07/2015	Japan	-	4	1
01/07/2015	South Korea	-	3	1
01/07/2015	UK	-	2	1
01/07/2015	USA	-	1	1
01/08/2015	Brazil	-	-	1
01/08/2015	Canada	-	-	1
01/08/2015	China	-	-	1
01/08/2015	France	-	-	1
01/08/2015	Germany	-	-	1
01/08/2015	Japan	-	-	1
01/08/2015	South Korea	-	-	1
01/08/2015	UK	-	-	1
01/08/2015	USA	-	-	1

Re: Joining Aggr and Rank to Count Distinct

sunny_talwar — Thu, 25 Feb 2016 13:34:02 GMT

Rank them based on what? Population? GDP?

Re: Joining Aggr and Rank to Count Distinct

tinkerz1 — Thu, 25 Feb 2016 14:02:59 GMT

I would like the below formula in a chart as an expression with DATE as the dimension

=if(Aggr(Rank(Country),Country)=2,Country)

This would then find the 2nd country in the list, from that information I want to find the count of distinct ID's for that country by date.

If I have this information I could populate a chart with many expressions look at different ranks.

This would get round the problem of not being able to have a stacked bar chart and a line on the chart.

Thanks,

ie many expressions setup as a stacked bar chart

=if(Aggr(Rank(Country),Country)=1,Country)

=if(Aggr(Rank(Country),Country)=2,Country)

=if(Aggr(Rank(Country),Country)=3,Country)

=if(Aggr(Rank(Country),Country)=4,Country)

=sum([budget])

Re: Joining Aggr and Rank to Count Distinct

tinkerz1 — Thu, 25 Feb 2016 14:17:13 GMT

it was as simple as

=if(num(Rank(Country))=2,count(distinct ID))

Re: Joining Aggr and Rank to Count Distinct

sunny_talwar — Thu, 25 Feb 2016 14:19:36 GMT

So you got it to work now?

Re: Joining Aggr and Rank to Count Distinct

tinkerz1 — Thu, 25 Feb 2016 14:55:54 GMT

I am afraid not quite.

I have now added a chart to te QVW as a demo on what I am after.

What I am trying to do is make flexable expressions using RANK, that means I can add a line to a combo stacked bar chart.

//=if(Rank(Country)=2,count(distinct ID))

But this wont return a result without country in the dimension, so the charts fails to stack.

But this will work.

=count(distinct if (Country='USA', ID)) but I can't make the country part of the formula flexable.

If I had 5 countries but 20 expressions made up that would be enough.

Re: Joining Aggr and Rank to Count Distinct

sunny_talwar — Thu, 25 Feb 2016 15:03:27 GMT

What about this?

=Aggr(if(Rank(Country)=2,count(distinct ID)), Date, Country)

Re: Joining Aggr and Rank to Count Distinct

tinkerz1 — Thu, 25 Feb 2016 15:10:35 GMT

OK thanks,

Because you wrapped the =if(Rank(Country)=2,count(distinct ID)) formula up in AGGR.

Did it mean that Qlik could associate that table to the date dimension and Country? Where my formula just returned a number?

Re: Joining Aggr and Rank to Count Distinct

sunny_talwar — Thu, 25 Feb 2016 15:14:42 GMT

Basically since your Rank was working when there was Country available, we created a in memory table using aggregate function with Country as one of the dimensions. I am not sure if Date is required or not, but since Date was the actual dimension, I added that in. You can try and check if this work as well or not:

=Aggr(if(Rank(Country)=2,count(distinct ID)), Date, Country)