Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our May 2021 end-to-end product release from Data Integration to Data Analytics is out! READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Qliksense_77
Contributor II
Contributor II

Convert datefield

Hi,

I have a date field where dec 2020 is in this format: 2020M12 and so on.

The same date field also contains one date in this format DD/MM/YYYY which is the newest date in the data.

I want to convert all dates to this format DD/MM/YYYY.

How to do this?

The reason for this, is that I need to show what is status now compared to same month last year. And I know how to do this, if all dates are in the standard date format.  

1 Solution

Accepted Solutions
Taoufiq_Zarra
Master II
Master II

@Qliksense_77 

If I suppose 2020M12 wil be 01/12/2020 you can use :

Date(Alt(Num(makedate(subfield(DateField,'M',1),subfield(DateField,'M',2),01)), Num(Date#(DateField, 'DD/MM/YYYY'))), 'DD/MM/YYYY') as NewDateField 

like this :

 

Data:

load *,Date(Alt(Num(makedate(subfield(DateField,'M',1),subfield(DateField,'M',2),01)), Num(Date#(DateField, 'DD/MM/YYYY'))), 'DD/MM/YYYY') as NewDateField inline [
DateField
2020M12
2020M10
2020M01
11/12/2020
01/01/2021
]

 

output:

Capture.PNG

if 2020M12 for example not 01/12 but 31/12 or other date you can change 1 by your number

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

4 Replies
Taoufiq_Zarra
Master II
Master II

@Qliksense_77 

If I suppose 2020M12 wil be 01/12/2020 you can use :

Date(Alt(Num(makedate(subfield(DateField,'M',1),subfield(DateField,'M',2),01)), Num(Date#(DateField, 'DD/MM/YYYY'))), 'DD/MM/YYYY') as NewDateField 

like this :

 

Data:

load *,Date(Alt(Num(makedate(subfield(DateField,'M',1),subfield(DateField,'M',2),01)), Num(Date#(DateField, 'DD/MM/YYYY'))), 'DD/MM/YYYY') as NewDateField inline [
DateField
2020M12
2020M10
2020M01
11/12/2020
01/01/2021
]

 

output:

Capture.PNG

if 2020M12 for example not 01/12 but 31/12 or other date you can change 1 by your number

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

Qliksense_77
Contributor II
Contributor II
Author

@Taoufiq_Zarra 

Thank you for fast reply.

Now I have this problem:

 6c9ed7e42f89433ab42593bead078631.png

Taoufiq_Zarra
Master II
Master II

@Qliksense_77 

this problem is not related to date format problems,but seems to be in the modeling or the calculation of measure

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Qliksense_77
Contributor II
Contributor II
Author

@Taoufiq_Zarra 

You are right. Your solution work. Thanks a lot😀