If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.
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?
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])
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);
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.