topic Re: Format issue in straight table with Date? in QlikView

Format issue in straight table with Date?

diane_yu — Mon, 09 Feb 2015 20:27:36 GMT

Hello,

I'm new to a Qlikview and I'm having an issue with formatting date field. In list box, CLOSED_DATE column formats anyway I wanted but not with striahg table. Currently, it display like '42019.7131...'. When I change the format to

=date(closed_date,'mm/dd/yyyy') -> it displays like 07/15/2015.....52/15/2015...49/15/2015 and so forth...

In database, it is a datefield.

Thank you for your support in advance!

Re: Format issue in straight table with Date?

JonnyPoole — Mon, 09 Feb 2015 20:39:45 GMT

If you are using this as a dimension, can you try this in the dimension expression:

date(Floor([CLOSED_DATE]))

Re: Format issue in straight table with Date?

diane_yu — Mon, 09 Feb 2015 20:51:51 GMT

Hi Jonathan,

Yes, I'm using it as a dimension. Your format displays in yyyy/mm/dd format. How do we change that to mm/dd/yyyy?

Thank you.

Re: Format issue in straight table with Date?

JonnyPoole — Mon, 09 Feb 2015 20:53:12 GMT

If the dates are accurate with the aforementioned formula, try tweak it as follows:

date(Floor([CLOSED_DATE]) , 'MM/DD/YYYY' )

Re: Format issue in straight table with Date?

diane_yu — Mon, 09 Feb 2015 20:58:46 GMT

Hooray! Thank you so much for your help!

Re: Format issue in straight table with Date?

JonnyPoole — Mon, 09 Feb 2015 21:01:00 GMT

Just to close the loop... the floor function rounds down number into integers. When you load in dates that show up as numbers with decimals, it means its a DateTime field. Qlik can work with DateTime fields but if you want to aggregate the time values into individual whole dates , then the floor() function does that. Then the date() function is used to format the number in the date format that you want.

Re: Format issue in straight table with Date?

diane_yu — Mon, 09 Feb 2015 21:09:03 GMT

Thank you. I also noticed that it is case sensitive with date function in Qlikview. I went back and changed to 'MM/DD/YYYY' and it displays correctly without using the floor function. I understand Qlikview is case sensitive but I did not realize it affects the Date function.

Thanks bunch!

Re: Format issue in straight table with Date?

JonnyPoole — Mon, 09 Feb 2015 21:14:52 GMT

'mm' in qlikview means minutes so you may have been geting

minutes/day/year ! and not month/day/year

Re: Format issue in straight table with Date?

diane_yu — Mon, 09 Feb 2015 21:17:10 GMT

Wow! I never imagined 'mm' indicatesas minutes. Thank you for the tip!

Re: Format issue in straight table with Date?

JonnyPoole — Mon, 09 Feb 2015 21:20:18 GMT

Here are some examples i dug up in the QlikView help for reference:

Examples (Date):

YY-MM-DD describes the date as 01-03-31.

YYYY-MM-DD describes the date as 2001-03-31.

YYYY-MMM-DD describes the date as 2001-Mar-31.

31 MMMM YYYY describes the date as 31 March 2001.

M/D/YY describes the date as 3/31/01.

W YY-MM-DD describes the date as 6 01-03-31.

WWW YY-MM-DD describes the date as Sat 01-03-31.

WWWW YY-MM-DD describes the date as Saturday 01-03-31.

Examples (Time):

hh:mm describes the time as 18:30

hh.mm.ss.ff describes the time as 18.30.00.00

hh:mm:tt describes the time as 06:30:pm

Examples (Timestamps):

YY-MM-DD hh:mm describes the timestamp as 97-03-31 18:30

M/D/Y hh.mm.ss.ffff describes the timestamp as 3/31/97 18.30.00.0000

Re: Format issue in straight table with Date?

diane_yu — Mon, 09 Feb 2015 21:42:26 GMT

You are so kind. Thank you for the information.