dynamically changing scale in text box

[Anonymous]

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

[swuehl · Accepted solution]

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

[Anonymous]

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

will let you know once it's worked

have a great day

[Anonymous]

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

reallly appreciate your help

[swuehl]

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

[Anonymous]

thankyou very much stefan

[MarcoWedel]

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

LOAD *,
     $(vFormatNumber(parameter)) as paramFormat;
LOAD pow(10,Rand()*14) as parameter
AutoGenerate 30;

regards

Marco

[#8]

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