- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Count distinct values based on year, month and date
Hi guys,
I am trying to compile a list box and bar chart that shows how many ID codes exists for a particular field.
So my dimension contains date/time information in the following format: 13/12/2011 01:27:40.
Each date can have multiple ID codes associated with it. So 13/12/2011 01:27:40 may have ID codes 2, 433, 2, associated with it.
I would like to show how many distinct codes exists per date, per month and per year, if possible.
Can anyone recommend the expression that would allow me to do this, please?
If you could post the expression rather than the .qvw file it would be appreciated as I am using the 'personal' edition and I have used up my free views of unrelated files.
Thank you in advance.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Ultimately depends how you want to view and interact with the data (which you do not specify above), but one way to do this is below:
Load Script:
LOAD * INLINE [
Date, ID
13/12/2011 01:27:40, A
13/12/2011 02:27:40, B
13/12/2011 01:27:40, A
22/11/2012 01:27:40, C
13/12/2012 01:27:40, A
13/12/2012 01:27:40, A
13/12/2012 02:27:40, A
13/12/2012 01:27:40, D
13/12/2012 01:27:40, D
];
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
If your list box is Date or Floor(Date/TimeField), you should be able to use this expression Count(DISTINCT ID)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
try this
= sum(aggr(count( distinct Codes),Date))
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
in bar chart add the date dimension and in expression type in
Count(Distinct [ID Fieldname])
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Ultimately depends how you want to view and interact with the data (which you do not specify above), but one way to do this is below:
Load Script:
LOAD * INLINE [
Date, ID
13/12/2011 01:27:40, A
13/12/2011 02:27:40, B
13/12/2011 01:27:40, A
22/11/2012 01:27:40, C
13/12/2012 01:27:40, A
13/12/2012 01:27:40, A
13/12/2012 02:27:40, A
13/12/2012 01:27:40, D
13/12/2012 01:27:40, D
];
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
May be Count(DISTINCT Codes) if your list box is Date
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi guys,
Thanks for the quick response.
I previously tried:
= sum(aggr(count( distinct [Code Id]),[ Date Time]))
count(Distinct([Code Id]))
but they don't work.
Both methods result in the following output sample:
| Code | = sum(aggr(count( distinct [Code Id]),[ Date Time])) | count(Distinct([Code Id])) |
13/11/2011 | 502 | 1 | 1 |
09/12/2011 | 620 | 1 | 1 |
18/12/2011 | 800 | 1 | 1 |
25/01/2013 | 005 | 1 | 1 |
25/01/2013 | 006 | 1 | 1 |
03/04/2014 | 227 | 1 | 1 |
03/04/2014 | 306 | 1 | 1 |
I don't think I explained myself well, so I apologize for that. What I would like to do is through some means of chart or table representation, show for each date (1st, 2nd, 3rd etc) how many distinct code IDs there are, and for each month (Jan, Feb, Mar) how many distinct code IDs there are, and then the same for each year. So it would look like a 'drill down' representation.
The Qlikview I am using has 'Document Properties' and 'Edit groups' disabled, so using drill down or cyclic methods in not available so I am seeking an alternate method representation. So ideally something like:
DATE | Number of distinct codes for each date |
1 | 3 |
2 | 6 |
3 | 1 |
4 | 15 |
5 | 11 |
6 | 2 |
7 | 3 |
MONTH | Number of distinct codes for each month |
1 | 17 |
2 | 24 |
3 | 24 |
4 | 53 |
5 | 44 |
6 | 18 |
7 | 55 |
YEAR | Number of distinct codes |
2009 | 170 |
2010 | 350 |
2011 | 541 |
2012 | 658 |
2013 | 245 |
2014 | 311 |
2015 | 102 |
Or maybe a chart representation?
Also, I neither have the 'Edit Script' option in 'File' nor on the design bar.
I hope I explained myself better than the last time? If not, let me know, guys.
Thank you so much to everyone who has responded.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Please see my previous examples above - the formula's are in the screenshot.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
1st in your example, you showed two dimension... are you using two dimensions or just one?
2nd if you don't have access to the script, I would suggest you to use a calculated dimension where you use Floor function to remove the time component of the date and time field
Date(Floor(Date/TimeField))
I guess you will use this in your cycle group and once you do this, I think Count(DISTINCT [Code Id]) should work for you