Number formatting not working

[#1]

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.

[mgranillo]

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')& ''))

[#3]

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')&''))

[sunny_talwar]

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

[#5]

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

[#6]

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))))

)

[sunny_talwar]

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)

[#8]

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.

[rubenmarin]

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.

[#10]

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!