Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
quriouss
Creator III
Creator III

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?

16 Replies
MK_QSL
MVP
MVP

Something like below

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

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

MK_QSL
MVP
MVP

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

quriouss
Creator III
Creator III
Author

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.

In QlikSense I'm talking about this dialog box (circled);

QlikNumber Formatting.png

Not applicable

You can also tweak with variable as well.

quriouss
Creator III
Creator III
Author

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

Not applicable

have you found the answer to this yet?

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

http://qlikviewcookbook.com

quriouss
Creator III
Creator III
Author

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

quriouss
Creator III
Creator III
Author

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>