Amount Format

[Anonymous]

Hello All,

I was trying to build a logic for last 12 months spend and below is the logic

sum(if(DATE(date#(paid_date_trend, 'YYYYMMDD') ,'DD/MM/YYYY')>=addmonths(today(),-12) ,$(vAmount) )

/$(vAmountDivisor),$(vFormat))

variables I used

1) vAmount =

if(vSpendType = 'Sourceable', (if(is_sourceable = 'Y', amount))

, if(vSpendType = 'SC Manageable', (if(is_sc_manageable = 'Y', amount))

  , amount))

2)vAmountDivisor =

if(vMoney ='Thousands',1000,

if(vMoney = 'Millions', 1000000,

if(vMoney = 'Billions', 1000000000,1)))

3)vFormat

if(vMoney ='Thousands','$#,##0.00K',

if(vMoney = 'Millions', '$#,##0.00M',

if(vMoney = 'Billions', '$#,##0.00B','$#,##0.00')))

                                   Data is not getting displayed,Can anyone let me know if the calculation needs any correction,

TIA

[Digvijay_Singh · Accepted solution]

- I think one bracket at wrong place, also I don't see num or money in the front, can you share the correct expression? -

sum(if(DATE(date#(paid_date_trend, 'YYYYMMDD') ,'DD/MM/YYYY')>=addmonths(today(),-12) ,$(vAmount)

/$(vAmountDivisor)),$(vFormat))

[Anonymous]

Thank you so much it worked by adding Money and adding a bracket after vAmountDivisor.

[clondono]

Hi Raman,

If you found the solution please close the thread by marking replies correct/helpful.

Thanks,

Carlos