creating a distinct date field

[hopkinsc]

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

[Miguel_Angel_Baeyens · Accepted solution]

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

[rubenmarin]

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;

[marcus_sommer]

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

- Marcus

[sunny_talwar]

Try this:

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

[Miguel_Angel_Baeyens]

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.

[hopkinsc]

of course! thanks Miguel!