16 Replies Latest reply: Feb 17, 2017 12:03 PM by Rob Wunderlich

Dynamically changing number formats?

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?

• Re: Dynamically changing number formats?

Something like below

IF([Invoice Value] >= 1000000, [Invoice Value]/1000000 &'M',

If([Invoice Value] >= 1000, [Invoice Value]/1000 &'K', [Invoice Value]))

• Re: Dynamically changing number formats?

Dual(

IF([Invoice Value] >= 1000000000, '\$'&[Invoice Value]/1000000000 & 'B',

IF([Invoice Value] >= 1000000, '\$'&[Invoice Value]/1000000 &'M',

If([Invoice Value] >= 1000, '\$'&[Invoice Value]/1000 &'K', '\$'&[Invoice Value]))),[Invoice Value])

• Re: Dynamically changing number formats?

These are all valid formulas but they convert the values into strings so don't get plotted onto charts, etc.  These expressions don't work in the formatting of the results.

• Re: Dynamically changing number formats?

You can also tweak with variable as well.

• Re: Dynamically changing number formats?

Thanks, but I can't see how to do this with variables - are you able to explain (or write some code to demonstrate?)

• Re: Dynamically changing number formats?

have you found the answer to this yet?

• Re: Dynamically changing number formats?

I create a variable in the script like this:

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

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

,num(\$1,'#,##0')

));

And then use it in expressions like this:

\$(vScaleNumber(sum(InvoiceValue)))

-Rob

http://qlikviewcookbook.com

• Re: Dynamically changing number formats?

Oooh, thanks, that's very useful.

And you can extend that to currency format as well, I guess;

"If ([Sale Location] = 'USA', num(\$1,"\$0,0"), num(\$1,"£0.0"))

• Re: Dynamically changing number formats?

Apologies for the long delay, but I just tried this in one of my dashboards, but it didn't work.

Should i be using the "expression" (\$(vScaleNumber(sum(InvoiceValue)))) in the script, or should it work in the measures of an object as well (that's where I'm trying to use it).

If I format the numbers in the script I will get text instead of numbers, so I shan't be able to plot the numbers on a chart, but if I add the measure to a chart it just gives me a blank.

<confused>

• Re: Dynamically changing number formats?

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

• Re: Dynamically changing number formats?

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://qlikviewcookbook.com

• Re: Dynamically changing number formats?

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

• Re: Dynamically changing number formats?

Thank you so much Rob.

Rj