Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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.

1 Solution

Accepted Solutions
Not applicable
Author

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

];

18-07-2017 13-54-36.jpg

View solution in original post

8 Replies
sunny_talwar

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

Kushal_Chawda

try this

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

qlikdeez
Specialist
Specialist

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

Count(Distinct [ID Fieldname])

Not applicable
Author

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

];

18-07-2017 13-54-36.jpg

Anil_Babu_Samineni

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Not applicable
Author

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.

Not applicable
Author

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

sunny_talwar

1st in your example, you showed two dimension... are you using two dimensions or just one?

Capture.PNG

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