If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.
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.
I have just added a Master calendar to my Data Load Editor and now things are working fine. I still do not know what went wrong with my previous setting. But adding the Master Calendar does help.
can you attach here your sample data, this would he helpful to find the correct solution.
Please see attached. Those cells in light blue (I have removed the information on purpose). Many thanks for your assistance.
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",
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?
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",
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.
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
This is from the main page.
My data comes from multiple excel files sent by external parties via FTP and below is the qvd -
And the excel file, I have attached it to you yesterday.
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)