topic Re: Count distinct values based on year, month and date in QlikView

Count distinct values based on year, month and date

Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Count distinct values based on year, month and date

sunny_talwar — Tue, 18 Jul 2017 11:51:30 GMT

If your list box is Date or Floor(Date/TimeField), you should be able to use this expression Count(DISTINCT ID)

Re: Count distinct values based on year, month and date

Kushal_Chawda — Tue, 18 Jul 2017 11:51:33 GMT

try this

= sum(aggr(count( distinct Codes),Date))

Re: Count distinct values based on year, month and date

Anonymous — Tue, 18 Jul 2017 11:53:06 GMT

in bar chart add the date dimension and in expression type in

Count(Distinct [ID Fieldname])

Re: Count distinct values based on year, month and date

Tue, 18 Jul 2017 11:53:08 GMT

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 02:27:40, A

13/12/2012 01:27:40, D

];

Re: Count distinct values based on year, month and date

Anil_Babu_Samineni — Tue, 18 Jul 2017 11:53:09 GMT

May be Count(DISTINCT Codes) if your list box is Date

Re: Count distinct values based on year, month and date

Tue, 18 Jul 2017 12:27:17 GMT

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.

Re: Count distinct values based on year, month and date

Tue, 18 Jul 2017 12:30:45 GMT

Please see my previous examples above - the formula's are in the screenshot.

Re: Count distinct values based on year, month and date

sunny_talwar — Tue, 18 Jul 2017 12:32:56 GMT

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