7 Replies Latest reply: Mar 13, 2015 12:38 PM by Philip Hand

# dynamically changing scale in text box

Hi qlikgurus,

I need to a logic for dynamic scaling , such as

in text box we showing                 total revenue ,

incremental profit

gross units sold

as values are very big we try to show them in billions but upon selections and to see granular data i need to come up with dymanic scale

have any here came up this kind of problem is there a way of writing expression such that based on selctions the symbols chnges from Billion to million to thousand

thanks have a good day

• ###### dynamically changing scale in text box

Maybe you could do it like this:

=num( sum( revenue) /pow(1000,floor(log10(fabs(sum(revenue)))/3)),'##0.000')

& pick(floor(log10(fabs(sum(revenue)))/3)+1,'',' thousand',' million',' billion',' trillion')

i.e. dividing your revenue value by some appropriate number and adding thousand, million, billion or trillion (or any other appropriate postfix).

Hope this helps,

Stefan

• ###### dynamically changing scale in text box

thanks stefans........appreciate it

will let you know once it's worked

have a great day

• ###### dynamically changing scale in text box

wow !!!  super stefan it's working great

and is thera way we can use them in chart

say suppose we have straight with values ranging for billions how change scale dynamicallu for those values as we did here

• ###### dynamically changing scale in text box

Not sure if I understand your setting, the expression should just work fine in a straight table chart expression.

I believe one limitation is that the expression will return a string, so you can only put it in where a string as result makes sense (for example, I don't think you can use it as bar chart expression, but you can use a bar chart with first expression sum( revenue) and display option bars, second expression like above with display options 'values on data points' and bars deselected. Also you might want to enable a logarithmic 'log scale' in axis tab).

Regards,

Stefan

• ###### dynamically changing scale in text box

thankyou very much stefan

• ###### Re: dynamically changing scale in text box

maybe one possibility to avoid the number-as-string limitation:

```SET vSize = Div(Log10(\$1),3);
SET vFormatNumber = Dual(Num(\$1/Pow(10,3*\$(vSize(\$1))),'#,##0.0')&' '&Pick(\$(vSize(\$1)),'K','Mil','Bil','Tril'),\$1);

\$(vFormatNumber(parameter)) as paramFormat;
AutoGenerate 30;
```

regards

Marco

• ###### Re: dynamically changing scale in text box

If you want to achieve this in the front end and keep the numeric format, place the symbol part (K,M,B) inside the number format string definition.

NUM(

SUM( [MeasureField] )

/

POW(1000 ,FLOOR(LOG10(FABS(SUM([MeasureField])))/3))

,'##0.0' & PICK(FLOOR(LOG10(FABS(SUM([MeasureField])))/3)+1,'',' K',' M',' B'))

Regards