Does anyone know how to customize number formatting in Qlik Sense, such that when you're formatting a measure total like $16,309,452,155.86, it can read instead like $16.31B, where both the "$" and "B" is appended to the decimaled number? This used to be a nice feature in Qlikview where the "K, M, or B" symbols could be added to the chart values, but having trouble finding the equivalent in Qlik Sense. This is the number format I would like to use, for instance, on the gauge chart visualization.
IF you click on the formatting section against the measure you can select custom and set your own formatting outside the standard supported ones
You have to do this manually. Try like this
=If(Sum(MeasureName)> 1000000000, Num(Sum(MeasureName)/1000000000, '$####.00B'),
If(Sum(MeasureName)> 1000000, Num(Sum(MeasureName)/1000000, '$####.00M'),
If(Sum(MeasureName)> 1000, Num(Sum(MeasureName)/1000, '$####.00K'),
Note: Replace Sum(MeasureName) with your actual expression.
Thanks for the reply! Would you mind sharing an example using the gauge chart? It doesn't look like the custom setting allows for formulas. There only seems to be a field for decimal and thousands separator symbols, and then the format patter in #,##0.00 form. This last field doesn't seem to yield the format I'm looking for, which is $16.3B, $12.3M, $543.1K, etc. I could be looking in the wrong area or misunderstanding, so perhaps an example or a snapshot of the customizations you're referring to, along with the final appearance in the gauge chart would help.
Jagan this is very helpful! I put in the formulas and it works like a charm on things like the KPI visualization! Thanks! I just have one point to clarify which I've outlined in the next conversation, regarding how to incorporate this format on the gauge chart.
Is there way to carry the conditional formula Jagan recommended, to formatting the range limits and central total amount on the gauge chart? So in the example snapshot below, I'd like the central number to read as $70.32M if in the millions, $70.32K if in the thousands, etc. Same thing with the ranges (e.g. $30K, $30M, $30B, etc. depending on the total) I don't want to hard code the format with an M, as the totals will vary upon updates and selections. A snapshot of the gauge format options would be most helpful.
Thanks for the reply. I need assistance with finding the equivalent in Qlik Sense rather than Qlikview. Do you have a sample qvf (not qvw) version that I can reference?
I thought to do that as well, but found that the conditional formula below is not fully rendered on the gauge chart. It ignores the "B, M, K" additions and only gives the decimal'd number. When I attempt to custom format the measures on the gauge chart, there is no function that can be entered, you can only hard code in the format. I don't want to hard code the "M" in custom format, in case the totals are over or less than the millions level (i.e. would be a B, K, or nothing)
Please feel free to send a snap shot of the formatting option values you've entered, along with a copy of the gauge chart or a qlik sense (qvf) file that I can reference, and I can check if I'm missing something in the configuration of that chart.