topic Re: Add distinct dates for Calendar Object in QlikView

Add distinct dates for Calendar Object

Anonymous — Wed, 17 Dec 2014 20:11:47 GMT

Hello to All,

So I have a long range of dates in my data and a lot of them occur more than once. I would like to use the Calendar Object but the drop down box will feature every single date. How can I reduce those to only the distinct dates so if the user chooses 10/1/14 it will populate all orders on that date instead of showing 40 10/1/14. I appreciate it thanks

Re: Add distinct dates for Calendar Object

maxgro — Wed, 17 Dec 2014 20:31:10 GMT

It seems the calendar object dowsn't show the same date more than once

Re: Add distinct dates for Calendar Object

sebastiandperei — Wed, 17 Dec 2014 20:37:26 GMT

May be you have a Date/Hour Field in Date field.

To know if it is like that, select Date/Time format, and you will see different hours for every date.

To solve it, you could add to replace in script:

Where it loads "Date_field" field, use Date(Date_field) as Date_field.

Or, if you want to solve in the object, go to expression (at the end of the list of available fields in box properties), and define it like:

Date(Round(Date_field))

Re: Add distinct dates for Calendar Object

Anonymous — Wed, 17 Dec 2014 21:22:20 GMT

Did you click on a certain feature? Mine is showing multiple dates

Re: Add distinct dates for Calendar Object

Anonymous — Wed, 17 Dec 2014 21:22:48 GMT

I have another field that has the date and time stamp but I only chose the field with the dates on it.

Re: Add distinct dates for Calendar Object

Anonymous — Wed, 17 Dec 2014 22:11:28 GMT

I've tried even using test data, how did you get it to show one value for each date?

Re: Add distinct dates for Calendar Object

sebastiandperei — Wed, 17 Dec 2014 22:46:53 GMT

Did you tryed what i suggest? The only reason to get repeated values in a list box is that these values aren't the same. Remember the fact that in Qlikview, date values has two meanings, one string and another number.For example:

For today, the field number value could be: 41990, and in if we have in the data:

date_field:

41990

41991

You should get in the list box:

List Box

1712/2014

18/12/2014

But, supose that you have the following values:

date_field:

41990,980810185

41990,18617981

41991,15654798

If your list box have, in Number tab, Date format, you will get:

List box:

17/12/2014

18/12/2014

Because of the two first dates are "nummerically" different.

Please, send a reduced .qvw file if you don't understand.