Date Fomat

[CFung]

Hi,

I have an issue related to date format. Appreciate if you can give some help. 

Per screen shot #1:

> This is my data source, from column C: the dates are showing as e.g. 21/10/2021 i.e. 21 Oct 2021

Per screen shot #2:

> You may see how I load the data to Qlik, and I have created 2 columns “Statement Date” and “Statement Date 2”

Per screen shot #3:

> I have created 2 bar charts, basically they are the same except one is with “Statement Date” and the other with “Statement Date 2”

> All looked fine until…

Per screen shot #4:

> I clicked on the first bar with “Jan 2021” on the “bar chart  in Statement Date 2”

> I saw the breakdown on the “ bar chart in Statement Date” but it’s showing as “01/10/2021” i.e. 1 Oct 2021 but not Jan 2021!

I think I have to define the date format of my data source to start with but I do not know how. Anyone can shed some light? Thanks.

Regards, CFung

[NitinK7]

can you attach here your sample data, this would he helpful to find the correct solution.

[CFung]

Please see attached. Those cells in light blue (I have removed the information on purpose). Many thanks for your assistance. 

[NitinK7]

try to add below script  at script editor.

Date("Stmt Date",'DD/MM/YYYY') as "Stmt Date",
Date(Date#(MonthStart("Stmt Date"),'MM/DD/YYYY'),'DD/MM/YYYY') as "Stmt Date2",

[CFung]

Hi NitinK7,
Not sure if I understood you correctly.
See #5 - I tried what you suggested

See #6 - I could not see expected results - column was empty.

May I ask if you could assist further?
Many thanks.

[NitinK7]

try below, its working for me

Date("Stmt Date",'DD/MM/YYYY') as "Stmt Date1",
Date(Date#(MonthStart("Stmt Date"),'MM/DD/YYYY'),'MMM-YYYY') as "Stmt Date2",
Date(MonthStart(Num#(Date#("Stmt Date",'MM/DD/YYYY'))),'MMM-YYYY') as "Stmt Date3",

[CFung]

Hi NitinK7, 

I have directly copy and paste your suggestion to Qlik

But I got different results:

> "Stmt Date2": still incorrect month and day format

> "Stmt Date3": completely empty. 

Can it be because of some settings issue? 

Appreciate if you can assist further. Many thanks. 

[NitinK7]

your data comes from database or excel file??

and please share your main page script like below or if possible then please attach here your .qvf file as well

[CFung]

Hi NitinK7, 

This is from the main page. 

My data comes from multiple excel files sent by external parties via FTP and below is the qvd - 

FROM [lib://ROB_P_Functional_BackOfficexxx/DailyOverdraft.qvd]
(qvd);

And the excel file, I have attached it to you yesterday. 

Many thanks! 

[NitinK7]

seems all are good.

please try once to change from 

SET DateFormat='M/D/YYYY';   to SET DateFormat='D/M/YYYY';  at main sheet

and use same sciprt taht i sent earliear .

or  if possible then can you share your .qvd file with limited data (same as excel)