couldn't open your attached workbook, however tried your calculation on sample data.
write a calculation like this:
Please find attached workbook, hope that helps.
Date_Coverted.twbx 34.9 KB
While I am not commenting on what is easier, you can prefix a zero for the numeric month number less than 10 in your SQL query itself.
DATE_PART ('year', Date) ||
CASE WHEN DATE_PART ('month', Date)<10 THEN "0" ELSE "" END ||
DATE_PART ('month', Date) AS YEARMONTH
I am not sure which database you are connected to. So, the syntax may differ but I think you get the idea.
There might be a similar way to do it but currently my data already has year and month as a string.
So it is slightly different in my case because where you are using 2 fields in the calculation mine is pulling from 1. Is there a way to accomplish the calculation from this 1 field?
I could change the SQL to pull year and month separate but just from learning perspective I'd like to see how it is done on a YEARMONTH field.
Also Hari I think that is a cool idea for the SQL! I will give that an attempt when I can.
It was a slow response from me but this worked! It was very cool seeing that Hari, thank you!
You are welcome.