Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have a bar chart with the below expression for measures. Basically, I want to display the values on the bar chart as "$1.23 M" or if the value is less than one million, it should display as "$4.56 K".
IF(<expression>/1000000 <=1,
Num(<expression>/1000,'$ #,##0.0 K'),
Num(<expression>/1000000,'$ #,##0.0 M')
)
When I put the same expression into a table, it displays the correct number format. When I have the expression in the bar chart, is just shows as "1.23". Any ideas as to why the bar chart doesn't reflect the correct number format?
Thanks!
It was not working because I wanted the dollar sign displayed also. I did find a solution. I removed all the excess formatting from the measure and had just the measure (without the IF and num function) and then changed the format to Custom. The very last comment (from Thomas Trolez) gave me the correct displayed format and I added the dollar sign in front.
Use 'A' in the custom formula
#,##0.00 A
Check the above link and for correct answer by Sunny T.
I replaced my expression with the one from that post (see below), but did not get the correct result. The Bar chart still does not show the correct number format.
If(<expression> >= 1000000000, Num(<expression>/1000000000, '$#,##0.0 B'),
If(<expression> >= 1000000, Num(<expression>/1000000, '$#,##0.0 M'),
If(<expression> >= 1000, Num(<expression>/1000, '$#,##0.0 K'),
Num(<expression>, '$#,##0.0'))))
As it is Qlik Sense, where are you writing your expression?
I wrote the expression in QlikView for a Text Box.
I guess if you have Auto Number formatting in Qlik Sense it will automatically convert the numbers.
Can you share your app?
I unable share my app, but the expressions are for measures, so in the bar chart properties panel, under measures. The number format for the measures it set to Auto, also. The expression returns the correct format in a table, but not in the bar chart.
It should work.
Cus I had similar issue and I'm testing in my other Qlik Sense Apps, it gives me K for 1000 when I use Auto Formatting.
Use the sample Data attached and try replicating same thing....
And you should note that in below measure '<expression>' is a variable I used for my Qlikview app.
So it is actually
If($(eVariable)>= 1000000000, Num($(eVariable)/1000000000, '$#,##0.0 B'),
If($(eVariable) >= 1000000, Num($(eVariable)/1000000, '$#,##0.0 M'),
If($(eVariable) >= 1000, Num($(eVariable)/1000, '$#,##0.0 K'),
Num($(eVariable), '$#,##0.0'))))
I stored the expression into a variable first as it was too long and then I used If condition as shown above.
You can try that too.....
First store your expression in Variable and then use that variable as a measure along with if condition. Whereever you have expression, replace it with your variable.
I replaced the expressions with a variable as you suggested and still get the wrong number format.
Can you at least share the data?
Auto formatting seems to be doing the job for you, no?