Number format

[upaliwije]

Hi Friends

I have in pivot table following expression.

IF(VALUELIST('GROSS PREMIUM NEW','REFUNDS NEW','NET PREMIUM NEW','GROSS PREMIUM RENEWAL','REFUNDS RENEWAL','NET PREMIUM RENEWAL','NET PREMIUM TOTAL','PREMIUM LAST YEAR','GROWTH %','BUDGET C.Y.','ACH.%')='ACH.%'

,(sum({$<PERIODID = {$(=Max(PERIODID))},MO_NM={'MOTOR'},YEAR =,QUARTER =,MONTH =,PERIOD =,DAY=>}[PREMIUM])/sum({$<PERIODID = {$(=Max(PERIODID))},MO_NM={'MOTOR'},YEAR =,QUARTER =,MONTH =,PERIOD =,DAY=>}[DAILY_BUDGET]))*100

Currently the Ach % is shown as 55 and I want Ach % to be shown as 55.3%. Kindly advise me ho to modify my expression

[uacg0009]

Hi UPALI,

in your expression, you don't need to * 100, and you need to use a num() function, like :

num(sum(xxx)/sum(xxx),'##.#%')

Aiolos Zhao

[upaliwije]

Thanks Zaho

Still it shows the same way. I am using a pivot table pls mot a text box

[upaliwije]

Hi Zaho

You formats works when I enter it as a individual expression. But when I couple it with if(valuelist...... Command it does not show in required format. Pls help

                                       Valuelist           Individual

REFUND RATIO NEW

52

51.6%

[upaliwije]

‌hi friends,

can can someone suggest me a solution please.

[Anil_Babu_Samineni]

Try this way?

IF(VALUELIST('GROSS PREMIUM NEW','REFUNDS NEW','NET PREMIUM NEW','GROSS PREMIUM RENEWAL','REFUNDS RENEWAL','NET PREMIUM RENEWAL','NET PREMIUM TOTAL','PREMIUM LAST YEAR','GROWTH %','BUDGET C.Y.','ACH.%')='ACH.%'

,Num((sum({$<PERIODID = {$(=Max(PERIODID))},MO_NM={'MOTOR'},YEAR =,QUARTER =,MONTH =,PERIOD =,DAY=>}[PREMIUM])/sum({$<PERIODID = {$(=Max(PERIODID))},MO_NM={'MOTOR'},YEAR =,QUARTER =,MONTH =,PERIOD =,DAY=>}[DAILY_BUDGET]),'#,##0.0'))*100

Or

[uacg0009]

Hi UPALI,

I think it will be the same whether you use it in valuelist or not...

Like below Anil said, in your table's properties - Number tab, what's your format setting you selected, you can try to use the expression default.

Aiolos Zhao

[upaliwije]

Thanks

When I apply your expression I get more than one decimal points

Pls help