Problem with dates

[Nemo1]

Hello everyone,

I have the following problem, I am importing an excel file to the data load editor, one of the columns of this file is a date, in this format: DD.MM.YYYY (SellingDate)

I need to extract from that date the MonthYear, so i used this formula: Date(SellingDate, 'MMM YYYY') as MonthYear

Then I go to the sheet to work on my dashboard, and I set a filter for MonthYear, but I get as many MonthYears as rows my dataset has. It is not aggregating correctly. It is taking MonthYear as unique values even tho they are the same.

what can i do?

[lennart_mo · Accepted solution]

Hi @Nemo1,

When you use Date(), Qlik still keeps all the information from the original date, so the days and possibly timestamps are kept, even if they're not shown in the format. You can see this, if you change the date format on your dashboard to something with days.

What you need to do is either use MonthName(SellingDate) or something like Date(MonthEnd(SellingDate), 'MMM YYYY'), where you evaluate each SellingDate before reformatting.

[QFabian]

Hi @Nemo1 @ here an option

with this excel file :

and with this script, using 'Date#' to get the field as a good date format:

Data:

LOAD

Date(Date#([SellingDate], 'MM.DD.YYYY') ) as [SellingDate],

    monthname(Date#([SellingDate], 'MM.DD.YYYY') ) as [MonthYear]

FROM [lib://AttachedFiles/test.xlsx] (ooxml, embedded labels, table is Hoja1);

you get this

[hardikpatel172]

One another solution (not so great) could be to concatenate with empty string e.g. doing it as below

Date(SellingDate, 'MMM YYYY') & '' as MonthYear 🙂 

[rwunderlich]

@hardikpatel172 Generally a bad idea because you lose the numeric value for sorting. But an interesting idea that must have an application somewhere!

-Rob

[hardikpatel172]

Yup, I totally agree, and that's why I mentioned "it's not a great solution" but when we face issues related to number and don't want to interpret that as a number, this could be an easy way. 

But true that we lose the numeric value. thank you!

[Chanty4u]

Try this 

Date(

    Date#(SellingDate, 'DD.MM.YYYY'),

    'MMM YYYY'

) as MonthYear

Or may be 

MonthName(Date#(SellingDate, 'DD.MM.YYYY')) as MonthYear

[Nemo1]

Hello Lennart, thanks for answering. 

that amazing, it worked! 

how should i addapt the formula if i wanna get the year? i have tried this:: Date(YearEnd(SellingDate), 'YYYY') or Date(Year(SellingDate), 'YYYY') but all i get is 1905 :(( i should be getting 2024 instead.

cheers

[Nemo1]

hey fabian, thanks for answering. 

when i do that, all i get is "-" 

do u know how could i extract just the year? 

cheers!!

[lennart_mo]

Hi @Nemo1,

I'm surprised your first approach didn't work. As for the second approach:
Year() returns the Year of the given date as a numerical value, not as a timestamp. So when you use date(), 2024 is evaluated as the number of days passed after '30.12.1899', which is the corresponding date for 0.

Try using the documentation provided by Qlik, it's actually quite helpful.

year - script and chart function | Qlik Sense on Windows Help