topic Re: Problem with dates in App Development

Problem with dates

Nemo1 — Tue, 23 Sep 2025 12:19:48 GMT

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?

Re: Problem with dates

lennart_mo — Tue, 23 Sep 2025 12:56:24 GMT

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.

Re: Problem with dates

QFabian — Tue, 23 Sep 2025 13:32:57 GMT

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

Re: Problem with dates

hardikpatel172 — Tue, 23 Sep 2025 14:54:23 GMT

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 🙂

Re: Problem with dates

rwunderlich — Tue, 23 Sep 2025 15:29:00 GMT

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

-Rob

Re: Problem with dates

hardikpatel172 — Tue, 23 Sep 2025 15:31:50 GMT

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!

Re: Problem with dates

Chanty4u — Tue, 23 Sep 2025 16:43:38 GMT

Try this

Date(

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

'MMM YYYY'

) as MonthYear

Or may be

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

Re: Problem with dates

Nemo1 — Thu, 25 Sep 2025 07:00:38 GMT

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

Re: Problem with dates

Nemo1 — Thu, 25 Sep 2025 07:03:40 GMT

hey fabian, thanks for answering.

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

do u know how could i extract just the year?

cheers!!

Re: Problem with dates

lennart_mo — Thu, 25 Sep 2025 08:11:30 GMT

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

Re: Problem with dates

MarcoWedel — Thu, 25 Sep 2025 08:52:26 GMT

Maybe better Date(MonthStart(... instead of Date(MonthEnd(...