Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
lthurman
New Contributor III

Formatting a date using the Expression Editor

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?

Thanks

1 Solution

Accepted Solutions
lthurman
New Contributor III

Re: Formatting a date using the Expression Editor

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.

View solution in original post

12 Replies

Re: Formatting a date using the Expression Editor

Are you sure BillingDate is formatted YYYYMM from the script? May be this is a front end format and the back end format is something else?

Partner
Partner

Re: Formatting a date using the Expression Editor

Hi Lorenzo,

Make sure you have these system variables correctly defined in your script.

SET LongMonthNames='Enero;Febrero;Marzo;Abril;Mayo;Junio;Julio;Agosto;Septiembre;Octubre;Noviembre;Diciembre';

SET MonthNames='Ene;Feb;Mar;Abr;May;Jun;Jul;Ago;Sep;Oct;Nov;Dic';

Regards.

lthurman
New Contributor III

Re: Formatting a date using the Expression Editor

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.

lthurman
New Contributor III

Re: Formatting a date using the Expression Editor

I did not have MonthNames defined in my script. I added them, but I still get the same result.

Partner
Partner

Re: Formatting a date using the Expression Editor

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.

lthurman
New Contributor III

Re: Formatting a date using the Expression Editor

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')

Re: Formatting a date using the Expression Editor

What do you get when you load the data like this

Num(BillingDate) as Num,

Text(BillingDate) as Text

lthurman
New Contributor III

Re: Formatting a date using the Expression Editor

Can I do this in the load script that loads QVD’s? If so, how?

Re: Formatting a date using the Expression Editor

You can, just need to load like this

LOAD *,

     Num(BillingDate) as Num,

     Text(BillingDate) as Text

FROM NameOfTheQVD.qvd (qvd);