topic Re: Formatting a date using the Expression Editor in App Development

Formatting a date using the Expression Editor

lthurman — Sat, 16 Nov 2024 09:04:32 GMT

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

Re: Formatting a date using the Expression Editor

sunny_talwar — Fri, 10 Nov 2017 17:34:38 GMT

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?

Re: Formatting a date using the Expression Editor

diegofcaivano — Fri, 10 Nov 2017 17:37:48 GMT

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.

Re: Formatting a date using the Expression Editor

lthurman — Fri, 10 Nov 2017 18:06:12 GMT

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.

Re: Formatting a date using the Expression Editor

lthurman — Fri, 10 Nov 2017 18:09:17 GMT

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

Re: Formatting a date using the Expression Editor

diegofcaivano — Fri, 10 Nov 2017 18:22:54 GMT

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.

Re: Formatting a date using the Expression Editor

lthurman — Fri, 10 Nov 2017 18:38:23 GMT

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

sunny_talwar — Fri, 10 Nov 2017 18:40:09 GMT

What do you get when you load the data like this

Num(BillingDate) as Num,

Text(BillingDate) as Text

Re: Formatting a date using the Expression Editor

lthurman — Fri, 10 Nov 2017 18:43:47 GMT

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

Re: Formatting a date using the Expression Editor

sunny_talwar — Fri, 10 Nov 2017 18:51:56 GMT

You can, just need to load like this

LOAD *,

Num(BillingDate) as Num,

Text(BillingDate) as Text

FROM NameOfTheQVD.qvd (qvd);

Re: Formatting a date using the Expression Editor

lthurman — Fri, 10 Nov 2017 20:06:00 GMT

Ok, so after adding the aliases to my load script, I tried this:

=Date(Date#(Num#(Text), 'YYYYMM'), 'MM YYYY')

and

=Date(Date#(Num), 'YYYYMM'), 'MM YYYY')

Same result.

Thanks for helping with this, by the way.

Re: Formatting a date using the Expression Editor

sunny_talwar — Fri, 10 Nov 2017 20:34:59 GMT

I was wondering if you can share a list box object for the two new fields that you created on my request....

Re: Formatting a date using the Expression Editor

lthurman — Wed, 15 Nov 2017 21:31:39 GMT

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.