Dynamically changing number formats?

[quriouss]

I have some numbers with a very wide range of values, from $10 to $10,000,000,000+

At the moment I am using an expression which is SUM ([Invoice Value])/1000000 to get the values into "Millions of dollars" but for very large and very small numbers this looks ugly.

I can format the underlying number so if it is over a Million, it is divided by a Million, otherwise divide it by 1000;

    ( IF SUM ([Invoice Value]) > 1000000, SUM ([Invoice Value]) / 1000000 , SUM ([Invoice Value]) / 1000 )

But I cannot seem to work out the correct number-formatting codes, so if the number is over $1M, it is shown with an M ($320M), otherwise it is shown with a k ($256k)

Just for reference, In Excel I would do; [>1000000]$0.0,," M";[>1000]$0.0,, "k";$0 is there something similar in Qlik Sense?

[#2]

Hello Rob,

Thanks for solution, this works for me but only thing I need include one decimal. like 1.2M, 860,3K etc. How can I do achieve this.

Thanks

Raj

[rwunderlich]

Hi Raju,

You should be able to specify the number of decimal places in the format mask. For only 1 decimal place:

SET vScaleNumber=if($1>1000000, num($1/1000000,'#,##0.00M')

,if($1>1000, num($1/1000,'#,##0.0K')

,num($1,'#,##0.0')

));

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

[#4]

Thank you so much Rob.

Rj

[#5]

Hi Rob,

thanks for sharing this as I was looking for exact that case of dynamic formatting.

I extended your version a bit to also format negative numbers and used dual() to have proper scaling of bars in a chart when using it, so that a 5,1 Mio value is shown bigger than a 100k value.

SET vScaleNumber=if($1>=1000000, dual(num($1/1000000,'#.##0,00 Mio'),$1),if($1>=1000, dual(num($1/1000,'#.##0,0K'),$1),if($1<=-1000000, dual(num($1/1000000,'#.##0,00 Mio'),$1),if($1<=-1000, dual(num($1/1000,'#.##0,0K'),$1),num($1,'#.##0,0')))));

But I ran into a problem using Set Analysis with it.

=$(vScaleNumber(sum({[Standard]<Example_ID={4, 6}>}Value)))

This is in my opinion the correct syntax as I use the exact same Set analysis elsewhere but not in a chart, but it doesnt work. I get 'No Data to display'. If I filter it manually in a listbox than it works fine.

If I filter only on one value like this it works fine:

=$(vScaleNumber(sum({[Standard]<Example_ID={4}>}Value)))

Any suggestions?

Thanks and regards,

Seb

[rwunderlich]

The problem is that any comma in a $DSE parameter is taken as a parameter separator. So after the comma is being taken as $2.  As a workaround, you need to escape and replace the comma, pr perhaps use $1$2 in your variable. That's something I haven't tried but it might work.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

[#7]

Hi Rob,

unfortunately the workaround with $1$2. Any suggestions how to escape and replace the comma?

Thanks,

Seb

[rwunderlich]

Here's a technique to escape the commas.

Comma problem (,) workaround for dollar sign expansion with parameters

-Rob