topic Change number format with expression in pivot table in QlikView

Change number format with expression in pivot table

Fri, 18 May 2012 10:26:26 GMT

Hi all,
In a pivot table i have a row with values in the format 1718,000. I want to change it in 1,718 with an expression. How I can do it?

Thanks

Change number format with expression in pivot table

Miguel_Angel_Baeyens — Fri, 18 May 2012 10:31:19 GMT

Hi,

First, I do not recommend you to use other than aggregation functions in the expressions (Only(), Sum(), Count(), etc). Using Num() or Money may slow down significantly your charts.

Use the Number tab in the chart properties to set the expected format.

Even better if you format your number fields in the load script.

Hope that helps.

Miguel

Change number format with expression in pivot table

janardhan — Fri, 18 May 2012 10:31:43 GMT

Hi ,

Goto Number Tab in chart properties , from there u can change

Hope that Helps.

Re: Change number format with expression in pivot table

Fri, 18 May 2012 10:38:47 GMT

I have to use expression...I know that it looks like this:
Num ( expense , '#,##0.00;(#,##0.00);-' )...but this '#,##0.00;(#,##0.00);-' how must be to obtain 1,718 from 1718,000?

Re: Change number format with expression in pivot table

Miguel_Angel_Baeyens — Fri, 18 May 2012 10:47:15 GMT

Hi,

In my case (you must take into consideration the local regional settings of your operating system), the following string works, and will work in the script also, by manually using the comma as thousand separator (while my regional is the period) and using the format string "#,##0":

=Num(Num#('1718,000', '0,0', ','), '#,##0;(#,##0)', '.', ',')

Hope that helps.

Miguel

Change number format with expression in pivot table

Fri, 18 May 2012 11:45:13 GMT

Thank Miguel!
I have used your string in this way:

Num(expense, '#,##0;(#,##0)', '.', ','))

and it works. All the values as 1718,000 now are in the format 1,718.
But I have a little problem only a value of expanse is 1845000,000 and I obtain 1,845,000 but I want only 1,845

Re: Change number format with expression in pivot table

Miguel_Angel_Baeyens — Fri, 18 May 2012 11:52:10 GMT

Hi,

If that's always the behavior (three trailing zeroes) you can do the following.

=Num(Num#('1718,000', '0,0', ',') / 1000, '#,##0;(#,##0)', '.', ',')

Hope that helps.

Miguel

Change number format with expression in pivot table

Fri, 18 May 2012 13:03:51 GMT

Thanks