topic Date Format Issue in App Development

Date Format Issue

erric3210 — Wed, 16 Mar 2022 12:26:33 GMT

Hi All,

I'm getting Date Format issue. I've tried all possible methods but not getting the desired format.

Due to this I'm getting duplicates in Dates. See this:

Here is the code I'm using:

Table_1:
LOAD// Date1 AS Date2,
// Date(Date#(Date1,'DD/MM/YYYY'),'YYYY-MM') AS Date2_1,
Date(Date1,'YYYY-MM') AS Date11,
Date(Date1,'YYYY-MM') AS Temp_Date
FROM
[..\Date1.xlsx]
(ooxml, embedded labels, table is Sheet1);

CONCATENATE(Table_1)
LOAD
// Date2 AS Date1,
Date(Date#(Date2,'YYYY-MM'),'YYYY-MM') AS Date11
FROM
[..\Date2.xlsx]
(ooxml, embedded labels, table is Sheet1)

WHERE NOT EXISTS(Temp_Date,Date(Date#(Date2,'YYYY-MM'),'YYYY-MM'));

Also, when using "Date1" table and modify the "date1" field using Date# function then it is returning blank.

I'm doing this:

Date(Date#(Date1,'DD/MM/YYYY'),'YYYY-MM') AS New_1

This is returning null date.

Attaching the sample data.

Please help and tell me the theory behind this issue.

Regards,

Eric

Re: Date Format Issue

Or — Wed, 16 Mar 2022 12:55:15 GMT

Date() does not change the underlying dates, it just formats the result in a specific manner, so for example July 1st and July 15th formatted as YYYY-MM would both return 2022-07 but have different underlying values. If you'd like to get month-level results, use e.g. MonthName(YourField) or MonthStart(YourField), which would actually change the underlying dates, before formatting as desired.

Re: Date Format Issue

Bill_Britt — Fri, 18 Mar 2022 11:59:59 GMT

HI,

Have you thought about creating a master calendar?

https://community.qlik.com/t5/QlikView-App-Dev/Creating-A-Master-Calendar/td-p/341286