What you want is typically achieved by having a measure with the same value that you would like to display. Since you want the numbers to be in thousands you simply divide the measure by 1000 (e.g. sum(Sales)/1000) and then adjust the format settings to use the desired separators;
Number formatting: Custom
Decimal sep: ,
Thousands sep: .
Hello Henry, thanks for your question. The suggestions here are valid, but I am not sure if that is what you are looking for.
Note: Doc (additional information)
Try changing the format pattern to #,##0K
Let us know if this is what you are looking for.
Note: Please mark the appropriate replies as helpful / correct so our team and other members know that your question(s) has been answered to your satisfaction.
There is currently no support for automated suffixes when formatting (we are looking into this). However it is still possible to achieve what you want by formatting directly in the measure expression.
Assuming you have a measure Sum(Sales) then the expression would look like this:
if(Sum(Sales) > 1000000, num(Sum(Sales)/1000000, '#.##0,##M', ',', '.'),
if(Sum(Sales) > 1000, num(Sum(Sales)/1000, '#.##0,##K', ',', '.'), num(Sum(Sales), '#.##0,##', ',', '.')))
Which would result in something like the following:
This formatting works on QlikView also quite good, but there is one problem, it does not show the letters at all on a bar graph!?
It should show a letter "t" after each of these numbers.. I used the expression that you gave:
=if(Sum(Value) > 1000000, num(Sum(Value)/1000000, '#.##0,##M', ',', '.'),
if(Sum(Value) > 1000, num(Sum(Value)/1000, '#.##0,##t', ',', '.'), num(Sum(Value), '#.##0,##', ',', '.')))
Is this even possible to put on to work in a QV chart?
And my numbers have also hundred, or smaller options when filtering down.
So forcing the number format to use letter "t" on a properties is not an option.
Hi bumped on this solution when I was looking for something similar in Qliksense to give me sum and averages based on a toggle.
The num() formatting works well unless a if condition is used. As soon as an If statement precedes the condition, the 'M' or 'k' identifier stops showing up on the garph. (The decimal point formatting remains the same).
Sigh, my excitement was premature. That technique works for the vertical axis of bar charts (and maybe other places), but it doesn't work in these places (and probably others):
- In the pop-up you get when you hover over data in any visualization
- In pivot charts
Yet another thing in Qlik that works sometimes.
I am not sure if this will address your concern, but we are releasing a new System Variable, NumericalAbbreviation, to allow one to set the numeric abbreviation for a measure. This will be in the April 2018 release of Qlik Sense due out this week I believe.
Stuff like this:
//Standard //SET NumericalAbbreviation='3:k;6:M;9:B;12:T;15:P;18:E;21:Z;24:Y;-3:m;-6:μ;-9:n;-12:p;-15:f;-18:a;-21:z;-24:y'; //G to B //SET NumericalAbbreviation='3:k;6:M;9:B;12:T;15:P;18:E;21:Z;24:Y;-3:m;-6:μ;-9:n;-12:p;-15:f;-18:a;-21:z;-24:y'; //Swedish currency //SET NumericalAbbreviation='3: tkr;6: mnkr;9: mdkr'; //100 potential SET NumericalAbbreviation='2: centi;4: decamilli;6: million';
hope this helps