Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Di
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.
If you are using this as a dimension, can you try this in the dimension expression:
date(Floor([CLOSED_DATE]))
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.
Di
If the dates are accurate with the aforementioned formula, try tweak it as follows:
date(Floor([CLOSED_DATE]) , 'MM/DD/YYYY' )
Hooray! Thank you so much for your help!
Di
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.
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!
Di
'mm' in qlikview means minutes so you may have been geting
minutes/day/year ! and not month/day/year
Wow! I never imagined 'mm' indicatesas minutes. Thank you for the tip!
Di
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