I'm a beginner Qlikview user and am still struggling with anything past making simple charts and list boxes.
In this case, I have a 'Market Size' KPI text box, with a formula of =sum(Amount)/1000000,'$#,###B'). In other words, I have a number that comes out in billions, with a nice B at the end of it. However, when I drill down on my chart to focus on individual businesses within that market, I'm really dealing with market sizing of millions, rather than billions. The result is that my KPI text box becomes an ugly $0.0000343B.
Does anyone know how I can instruct the KPI text box to switch to millions, and switch the B with an M at the end, if the numbers become small enough? I have a hunch that I need to rewrite the expression formula as an if statement, but I'm not sure how.
Thanks in advance!
Thank you, Alessandro! This worked perfectly.
As a follow-up question, if I encountered a number that was less than $1 million, how would I modify that expression to add a second condition to express that number in thousands?
might be a bit late
but maybe also helpful:
SET vSize = Div(Log10($1),3);
SET vFormatNumber = Dual(Num($1/Pow(10,3*$(vSize($1))),'#,##0')&' '&Pick($(vSize($1)),'K','Mil','Bil','Tril'),$1);
$(vFormatNumber(parameter)) as paramFormat;
LOAD pow(10,Rand()*14) as parameter
Thanks for the solution! But it seems that it won't work for negative value. Anything that I need to change so that it can format both positive and negative value? Thanks again!
Manage to get it to work after change the following by include the fabs function:
SET vSize = Div(Log10(fabs($1)),3);