Hello Qlik Sense Community,
I need to convert a date variable from this format 201701 to Jan 2017. I tried using Date and Date#, but instead of getting the short month and year, I only get the year. Our variable name is BillingDate and I use it like this:
Date(Date#(BillingDate, 'YYYYMM'), 'MMM YYYY')
I've also tried using MM as part of the output format, but I still only get the year. Any ideas as to where I may be going wrong?
Sorry, I forgot to follow up, but we finally figured it out. It seems that our original formula was trying to create a date from a string. We should have been trying to create a date from a number. The correct formula to create our MMM/YYYY date is:
=date(makedate(num(left(BillDate,4)),num(right(BillDate,2))),'MMM YYYY') where Billdate is a sring of the format, YYYY/MM.
Thanks for all your help.
Make sure you have these system variables correctly defined in your script.
I believe the format is the same. I'm actually loading the data from a QVD and if I just use BillingDate without any formatting, I get 201701, for example.
OK. Try to confirm that the original value is really a number by applying Num#() function to interprete it as such, and then checking its data-type in the model tables viewer or any other way.
I’ve looked at the Data Model Viewer and the data is formatted as stated above. I also tried using num# and the results are the same, year, no short month.
=Date(Date#(Num#(BillingPeriodVal), 'YYYYMM'), 'MMM YYYY')