Change number format with expression in pivot table

[#1]

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

[Miguel_Angel_Baeyens]

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

[janardhan]

Hi ,

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

Hope that Helps.

[#4]

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?

[Miguel_Angel_Baeyens]

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

[#6]

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

[Miguel_Angel_Baeyens · Accepted solution]

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

[#8]

Thanks