topic creating a distinct date field in QlikView

creating a distinct date field

hopkinsc — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

i have a date in the following format..

2016-10-21 13:55:43 +01:00

i have transformed it into just a date and then transformed the new date field to a MMM-YY field.

i am getting multiple MMM-YY values in the field and am trying to get distinct values but i cant seem to be able to do it.

can anyone help please?

I guess it could be something to do with the initial way i am deriving the date from the above format.

i have attached a sample.

many thanks

Re: creating a distinct date field

Miguel_Angel_Baeyens — Wed, 30 Nov 2016 12:44:39 GMT

Yes, you see that because the value you represent is MMM-YY, but the value actually stored is the full date, so you need to remove the day using, for example:

MakeDate(Year(DateField), Month(DateField)) AS Date_MMM-YY

Re: creating a distinct date field

rubenmarin — Wed, 30 Nov 2016 12:45:10 GMT

Hi Chris, dates has 2 values: the shown value and the internal value. The interval value is the date in number, the unit (1) represents a day, and fractions of the units represents the time.

In your case you see the same value many times because they have a different internal value (day, hour, minutes), to store the same internal value you can set every date to the first day and second of the month:

date(Monthstart(Date),'MMM-YY') as MonthYr;

Re: creating a distinct date field

marcus_sommer — Wed, 30 Nov 2016 12:45:18 GMT

You need to cut the timepart - formatting alone isn't enough. Try: date(floor(YourTimestampField)).

- Marcus

Re: creating a distinct date field

sunny_talwar — Wed, 30 Nov 2016 12:45:39 GMT

Try this:

Date(MonthStart(Date),'MMM-YY') as MonthYr;

Re: creating a distinct date field

Miguel_Angel_Baeyens — Wed, 30 Nov 2016 12:48:30 GMT

That would be if time was the issue, but what he wants to remove is the day completely, so the Floor() will not work in this case because the day is part of the integer value of the date. But yes, it is all about how QlikView differences between date representation and date interpretation.

Re: creating a distinct date field

hopkinsc — Wed, 30 Nov 2016 12:52:29 GMT

of course! thanks Miguel!