Create buckets and show charts based on few columns

[Oero]

I have my data which looks like - 

Name - a,b,c,d,e,f,... and so on

id - id1, id2, id3, id4, id5... and so on

I want to show a bar chart which will be like - see the image

Logic - I want to show 5 unique  ids are appearing for 6 months, 4 unique ids are appearing for 7 months like that

please suggest me some ideas, bar chart is not mandatory if u have some better ideas please help

[Oleg_Troyansky · Accepted solution]

Hi,

So, you are trying to build a distribution curve of number of IDs based on the number of Months that they appear in.

This is done with the use of the AGGR() function. Something along these lines:

Dimension: AGGR(count(distinct Month), ID) 

Measure: count(distinct ID)

In order to learn how to use the AGGR() function and Set Analysis for advanced analytics, join my on-line session on Set Analysis and AGGR() on March 1st at the virtual Masters Summit for Qlik.

Best,

[Oero]

thanks for the solution, I just wanted to ask, so how can we read the chart, these numbers signify months count? so if there is (3, 7) which means there are 7 rules (y axis) which are found repeated for 3 months (x axis). Is this correct?

[Oleg_Troyansky]

Yes, this is exactly right. If you want to add labels to these dimensions for better readability, you can concatenate the calculated numbers with the desired labels, for example:

 AGGR(count(distinct Month), ID) & ' months'

Cheers,

[Oero]

Thanks a lot, and how can we show the id's name and the months name? suppose I create a table named - details, so we have already 2 columns (which you have answered above) we need 2 more columns i.e. months name and Id's name, 
I tried this, but its not working - concat(distinct month, ', ')
could you please help

[Oleg_Troyansky]

Concat is the right approach - it should work perfectly for IDs. For Month names, you need to convert month numbers to associated Month Names. You can just load a simple table with 12 rows and associate numbers from 1 to 12 to their corresponding names. Then, the function concat should also give you the desired result.

Sign up for my Set Analysis and AGGR() session next week - you will learn many advanced tricks like this one, and more.

[Oero]

I have one doubt, so in the X axis I have numbers from 1-6 right? and in Y axis - count of id's, 
what does the number in X axis signify? I thought it is basically months count. So if I have (3,8) that means 8 ids are repeated for 3 months (for example - jan, july, sep)
but as you are suggesting, according to that it seems like (3,8) - 8 id's for Mar? whch one is correct sir?
what I want to show is - 3 id's are there which are found repeated for 8 months (ex - jan, jul, aug, sep, feb, mar, nov, dec)
I am not that expert, sir could u please help