topic Re: Change Date format in Pivot Table in QlikView

Change Date format in Pivot Table

Anonymous — Wed, 25 Feb 2015 08:31:48 GMT

Hi,

How can i change the date format in the pivot table?

Now it shows dd,mm.yyyy.

How can i change it to dd.mm.yyyy?

Re: Change Date format in Pivot Table

anbu1984 — Wed, 25 Feb 2015 08:38:06 GMT

In Script,

Date(Date#(Dato,'DD,MM.YYYY'),'DD.MM.YYYY') As Dato

Re: Change Date format in Pivot Table

Wed, 25 Feb 2015 08:39:43 GMT

hi,

go to chart property->

Date(Date,'DD.MM.YYYY')

Re: Change Date format in Pivot Table

Wed, 25 Feb 2015 08:39:51 GMT

In your Pivot Table, Call your date as an expression (eg. =Date)

Go to Properties->Number Tab->Select Date(Left side)->then select 'Date' in Number Format Settings->Specify the format you require.

Re: Change Date format in Pivot Table

Anonymous — Wed, 25 Feb 2015 09:21:22 GMT

Now the date/dato displays two places.. I also tried to remove the "Dato" dimension but then i cant move the "Dato" expression under "Måned"

Re: Change Date format in Pivot Table

Anonymous — Wed, 25 Feb 2015 09:26:38 GMT

It does not work.. now I get "//Error in calc..." in every date cell.

Re: Change Date format in Pivot Table

Thu, 26 Feb 2015 03:56:25 GMT

hi ,

send a test file of qvw.

Regards

Vimlesh

Re: Change Date format in Pivot Table

jagan — Thu, 26 Feb 2015 04:03:33 GMT

Hi,

Try like this in script

Load

Date(Date#(DateFieldName,'DD,MM.YYYY'),'DD.MM.YYYY') AS Date_Format

FROM DataSource;

Now you can use this field as dimension in the chart.

Regards,

Jagan.

Re: Change Date format in Pivot Table

Thu, 26 Feb 2015 04:12:06 GMT

hi ,

clear one more thing your date is data source should be in date format not in text.

if you will use num(date) function then your date should be in number format.

if it is not showing in number then go to script and use makedate function to convert Text date format

prefer attached QVW file

Regards

Vimlesh

Re: Change Date format in Pivot Table

timanshu — Thu, 26 Feb 2015 04:41:25 GMT

Hi Marius,

Just Put this in dimension with Date field for which you want to display different format.

Date(Dato,'DD.MM.YYYY')

It works , as I checked.

If it still doesn't work for you then kindly attach the qvw.

Re: Change Date format in Pivot Table

Thu, 26 Feb 2015 07:14:31 GMT

This is the basic Cleaning ETL process that we do at script level to maintain standard format througout the application. You must change the format in the script as suggested by my fellow mates.

Thanks,

Singh

Re: Change Date format in Pivot Table

timanshu — Thu, 26 Feb 2015 07:57:19 GMT

Hi Angad,

Yes you are absolutely right. But depending on the situation, if anyone requires to show multiple date formats for a Date Field with in a application.And that too change frequently, then It's good to specify format at front end.

Example is As @Marius's requirement shows that he wants to show Dato (a date field) into different formats into a single chart.

Even, we can do this by defining more than one formats by assigning different name for same date field at script level.But again if the format requirement changes frequently, then I think we should do it on front end.

Don't you think so?

Re: Change Date format in Pivot Table

Anonymous — Thu, 26 Feb 2015 07:57:42 GMT

Test file?

a copy of the qvw?
The file is on over 700 mb and contains over 300 million rows...

Re: Change Date format in Pivot Table

Anonymous — Thu, 26 Feb 2015 08:01:52 GMT

Date(Dato,'DD.MM.YYYY') Worked, Thanks !

Re: Change Date format in Pivot Table

Thu, 26 Feb 2015 08:07:51 GMT

Indeed, you are right. If situation demands then we have to make adhoc changes. May be I did not come across any such scenario in which Date format gets changed that frequent.

Happy that Marius got his answer from the community.