topic Re: Date Format in App Development

Date Format

Fri, 18 Aug 2017 11:12:38 GMT

Hello Everyone,

I have an issue which I know is very easy to solve, there's just something I am not quite getting right.

I have a Filter Pane which filters the dimension date. The date is showing up in the Qlik Sense formate, i.e 42370, 42401 etc.

I would like this date to be displayed in the DD MMMM YYYY format.

I have tried to do so in using the following expression: Date#([Date][, 'DD MMMM YYYY']) (Date is the name of the field containing dates). However, there's an error in my expression.

Please can someone tell me what I am doing wrong?

Thank you all in advance,

Alison

Re: Date Format

antoniotiman — Fri, 18 Aug 2017 11:24:23 GMT

Hi Aliso,

try

Date(Date,'DD MMMM YYYY')

Regards,

Antonio

Re: Date Format

Fri, 18 Aug 2017 11:30:54 GMT

Hello Antonio,

Thank you for your response.

I have tried your suggestion. When I enter it, the script has no error but when it is applied there are no longer any values found in the filter pane.

Re: Date Format

sunny_talwar — Fri, 18 Aug 2017 11:31:06 GMT

For formatting, you just need a date function and date#()... date#() is used for interpretation (i.e. when QlikView is having a difficult time understanding that you date field is a date field. So in your case, you already have a date (in the shape of a number), so you need to use Date() function.

Also, the format doesn't have to be surrounded by square brackets

Date([Date], 'DD MMMM YYYY')

Re: Date Format

sunny_talwar — Fri, 18 Aug 2017 11:31:50 GMT

May be try this

Date(Num#(Date), 'DD MMMM YYYY')

Re: Date Format

Fri, 18 Aug 2017 11:33:05 GMT

Hi Sunny,

Thank you.

I am aware of all that. And yet, I am still having difficulty. As I said above in my response to Antonio, after applying that script I am left without any values in the filter pane. Why is that?

Re: Date Format

its_anandrjs — Fri, 18 Aug 2017 11:35:08 GMT

Hi,

Try this ways

Ex:-

LOAD *, Date(NewNUM , 'DD MMMM YYYY') as NewDate;

LOAD *, Date(NumDate) as NewNUM;

LOAD * Inline

[

NumDate

42370

42401

];

See attached

Regards

Anand

Re: Date Format

its_anandrjs — Fri, 18 Aug 2017 11:39:50 GMT

Are you using this expression in the dimension be clear you can use this on the dimension

Ex:-

Date ( [Date], 'DD MMMM YYYY' )

Date( YourdateFieldHere, 'DD MMMM YYYY')

Be clear on the field name and date syntax.

Regards

Anand

Re: Date Format

Fri, 18 Aug 2017 11:44:11 GMT

Hello Anand,

Thank you for your response.

That does work, but it seems a little over-complex and it would require quite a lot of time, inputting the dates manually into the data load editor. Is there no solution that less taxing?

Re: Date Format

sunny_talwar — Fri, 18 Aug 2017 11:46:28 GMT

Did you try this?

Date(Num#(Date), 'DD MMMM YYYY')

Re: Date Format

Fri, 18 Aug 2017 11:47:28 GMT

Hello,

Yes, my date field is called Date. I have now, however, renamed it DateMonthYear to avoid any confusion. But I still have the same issue.

Re: Date Format

Fri, 18 Aug 2017 11:48:30 GMT

Brilliant! Thank you Sunny.

Re: Date Format

its_anandrjs — Fri, 18 Aug 2017 11:52:20 GMT

Thanks,

It is ok if you cleansing the field in the script part and then use it on the dashboard. Or else others also suggesting correct you have to use Date(NewNUM , 'DD MMMM YYYY') in your dimension part.

Ex:-

Date(YourDateField, 'DD MMMM YYYY')

Regards

Anand