topic Re: How do I format a date in a pivot table ? in QlikView

How do I format a date in a pivot table ?

Thu, 17 Sep 2015 10:15:08 GMT

Hi All,

I have a sheet object (a pivot table) which displays a date as YYYYMMDD. How can I format it to display as DD/MM/YYYY ?

Thanks

Re: How do I format a date in a pivot table ?

alexandros17 — Thu, 17 Sep 2015 10:27:21 GMT

Split (with string functions MID) into DD MM YYYY thes use Makedate function

Re: How do I format a date in a pivot table ?

Thu, 17 Sep 2015 10:29:41 GMT

Can you give me an example please ? I'm assuming this is in the LOAD statement ?

Re: How do I format a date in a pivot table ?

marcus_sommer — Thu, 17 Sep 2015 10:32:04 GMT

I suggest to do this (maybe as an additionally field) within the script maybe in a master-calendar instead in (many?) gui-objects, see also: How to use - Master-Calendar and Date-Values.

- Marcus

Re: How do I format a date in a pivot table ?

alexandros17 — Thu, 17 Sep 2015 10:32:50 GMT

MakeDate(Mid(myDate,1,4), Mid(myDate,5,2), Mid(myDate,7,2))

let me know

Re: How do I format a date in a pivot table ?

Anonymous — Thu, 17 Sep 2015 10:36:23 GMT

Two ways:

1) In the script at main tab:

Set DateFormat='DD/MMM/YYYY';

2) Right Click on Pivot table and go to number tab, select the field from left list and override the date format.

Re: How do I format a date in a pivot table ?

Thu, 17 Sep 2015 10:39:09 GMT

The date field doesn't appear in the list in the left as it's a dimension, not an expression.

Re: How do I format a date in a pivot table ?

Anonymous — Thu, 17 Sep 2015 10:42:58 GMT

oky, In your dimension tab, say Date is your field:

try this:

date(YourDate,'DD/MMM/YYYY')

Re: How do I format a date in a pivot table ?

Colin-Albert — Thu, 17 Sep 2015 10:45:50 GMT

Has the data been loaded as a date field?

Have a look at this post which shows how to load dates correctly. Get the Dates Right

Re: How do I format a date in a pivot table ?

sasiparupudi1 — Thu, 17 Sep 2015 10:48:17 GMT

try

date(date#(Yourdate,'YYYYMMDD'),'DD/MM/YYYY')

hth

Sasi