Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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')& ''))
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')&''))
Syntax looks right to me...would you be able to share screenshot to show that it isn't working?
You can see the number 331 on the right hand side. The number dissappears when i add the &' ' formatting
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))))
)
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)
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.
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.
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!