Convert datefield

[Qliksense_77]

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.  

[Taoufiq_Zarra · Accepted solution]

@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:

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

[Qliksense_77]

@Taoufiq_Zarra 

Thank you for fast reply.

Now I have this problem:

 

[Taoufiq_Zarra]

@Qliksense_77 

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

[Qliksense_77]

@Taoufiq_Zarra 

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