10 Replies Latest reply: Jul 15, 2016 7:20 AM by Ruben Marin

# Number formatting not working

Hi Guys,

I am using a calculated field that needs to change based on toggle between total and average.

I am trying to get the format dynamically for totals in million and average in thousands. The formatting is not working.

Please find below the code i am using.

- Aggregate is the toggle between total and average using a variable extension

- i use the year code is to select the max date always.

//Code

if(Only({\$<Year={\$(=Max(Year))}>}Aggregate)=1,

num((Sum({\$<Year={\$(=Max(Year)}>}Contribution)/1000000),'#.##0,##M'),

if(Only({\$<Year={\$(=Max(Year)}>}Aggregate)=2,

num((Sum({\$<Year={\$(=Max(Year))}>}Contribution)/

Count({\$<Year={\$(=Max(YEar)}>}[ACCT])),'#,##0.00K')))

Any help would be appreciated.

• ###### Re: Number formatting not working

try

if(Only({\$<Year={\$(=Max(Year))}>}Aggregate)=1,

num((Sum({\$<Year={\$(=Max(Year))}>}Contribution)/1000000),'#.##0,##M')& '',

if(Only({\$<Year={\$(=Max(Year))}>}Aggregate)=2,

num((Sum({\$<Year={\$(=Max(Year))}>}Contribution)/

Count({\$<Year={\$(=Max(Year))}>}[ACCT])),'#,##0.00K')& ''))

• ###### Re: Number formatting not working

Hi Michael,

This works great!! But in cases  where i need to add two set expressions it doesn't work. Am I doing something wrong here?

if(Only({\$<Year={\$(=Max(Year))}>}Aggregate)=1,

num((Sum({\$<Year={\$(=Max(Year))}>}CONTRIBUTION)+

Sum({\$<Year={\$(=Max(Year))}>}WITHDRAWL))/1000000,'#,##0.00M')&'',

if(Only({\$<Year={\$(=Max(Year))}>}Aggregate)=2,

num((Sum({\$<Year={\$(=Max(Year))}>}CONTRIBUTION)+Sum({\$<SnapshotYr={\$(=Max(YEAR)}>}WITHDRAWL))/

count({\$<Year={\$(=Max(Year))}>}ACCT),'#,##0.00')&''))

• ###### Re: Number formatting not working

Syntax looks right to me...would you be able to share screenshot to show that it isn't working?

• ###### Re: Number formatting not working

You can see the number 331 on the right hand side. The number dissappears when i add the &' ' formatting

• ###### Re: Number formatting not working

Even in cases like this, the toggle for total and average does not work.It simply maintains the total value even for average.

Maybe the way i am using the sum and + functions are not really right?

if(Only({<Year= >} valuelist('Net','Total')) = 'Net',

if (Only({<Year= >}Aggregate)=1, (Sum({<SnapshotYr= >}[CONTRIBUTION])+Sum({<SnapshotYr= >}WITHDRAWL)),

if (Only({<Year= >}Aggregate)=2,((Sum({<SnapshotYr= >}[CONTRIBUTION])+Sum({<SnapshotYr= >}WITHDRAWL)))/count({<Year=>}ACCT)),

Sum({<SnapshotYr= >}[CONTRIBUTION])+Sum({<SnapshotYr= >}WITHDRAWL))))

)

• ###### Re: Number formatting not working

Is it may be due to the size of the object? Have you tried resizing the object to be a little larger (try increasing the length)

• ###### Re: Number formatting not working

No that doesn't work.

It's strange that it works well in a field like this:

num(Count({\$<Year={\$(=Max(Year))},Assets={">0"}>}ACCT)/1000,'#,##0K')& ''"

but not in

if(Only({<Year= >}valuelist('Acct','Ctrb'))='Acct', num(count({<year= , ASSETS={">0"}>}ACCT)/1000,'#,##0K')&''"

It looks to me like using a valuelist is causing this problem because everywhere i have a valuelist it doesnt work.

• ###### Re: Number formatting not working

Hi Sowmya, I think you can't set a dynamic numeric format for KPI objects, if you set the number to 'Auto' it will automatically add 'k, M, B...'.

You can try to simulate the object using a text box, there you can set number format dynamically for each expression and you can set a different size and/or color for each expression used.

EDIT: Well, seems you can. I tested Num(1000, '0,0k') and didn't worked but Num(Sum(A), '0,0k') worked.

• ###### Re: Number formatting not working

Hi Ruben,

The dynamic measure setting for KPIs actually works. It overrides the default.

But unable to figure out why it doesn't work with a valuelist!

• ###### Re: Number formatting not working

Sometimes I faced that QV with selections didn't get the valuelist value, in those situations changing the if to If(RowNo()=1... ) solved the problem.

Is there a possibility to upload a sample?