I have some values in a field (revenue) and I'd like to be able to display the values in a condition dependent way. For example, if the number is less than $1000 then display the full number, but for $1000 and over, I'd like it to be displayed as $1k, and for $1,000,000 and over I'd like it to display as $1m.
I would like to use something like:
IF SUM([Store Total Revenue]) > 1000 THEN SUM([Store Total Revenue])/1000 ELSEIF (SUM([Store Total Revenue])) > 1000000 THEN SUM([Store Total Revenue])/1000000 END
But then modify it so that it uses a string instead of the actual value, e.g.
IF SUM([Store Total Revenue]) > 1000 THEN STR("$",SUM([Store Total Revenue])/1000, "k") ELSEIF (SUM([Store Total Revenue])) > 1000000 THEN STR("$",SUM([Store Total Revenue])/1000000,"m") ELSE (STR(SUM[Store Total Revenue])) END
But it does not seem to want to cast the return value of SUM into a STR.
Can anyone help me with this, either w/ writing the above function or a totally different way of doing it?
Thanks
Brad
Hi Brad,
this is one of my favourite quick fixes! Use the following calculated field (and insert your own field names where necessary):
// for numbers > 1 million
IF SUM([VALUE])>=1000000
then (STR(ROUND((SUM([VALUE])/1000000),1))) +"M"
//for numbers > 1 thousand
elseif ((SUM([VALUE])<1000000) AND (SUM([[VALUE])>1000))
then (STR(ROUND((SUM([VALUE])/1000),1))) +"k"
//for numbers < 1000
else STR(SUM([VALUE])) end
