Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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,
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,
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?
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,
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
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.
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