Changing Monetary Values to Numerical Abbreviations on X-Axis

[snightengale]

Hi, 

I'm building a scatter plot and have large values of money on the X-axis. I want to change those values from being $20,000,000,000 to $20B but can't seem to get that to work.

Measure:  Sum({<ModelDiscretionaryAcctFlag={'N'}>} AccountValue)

Number formatting: Money, $#,##0;-$#,##0

Tried using Number formatting: Custom Format: $#,##0.0a

Neither seems to give me what I want. Any thoughts? 

* 

[priscilarubim]

Hi @snightengale.

First, I would like to ask whether all the numbers are in the billions scale. If that is the case, you can write the formatting directly in the measure expression, but leave the chart number formatting set to Auto.

For example:

Num(
    Sum({<ModelDiscretionaryAcctFlag={'N'}>} AccountValue) / 1000000000,
    '$#,##0B;-$#,##0B'
)

This scales the value to billions and displays values like:

20,000,000,000 -> $20B
69,000,000,000 -> $69B

Hope this helps 🙂

[Chanty4u]

Try this 

Num(

    Sum({<ModelDiscretionaryAcctFlag={'N'}>} AccountValue)/1000000000,

    '$#,##0.0'

) & 'B'

[snightengale · Accepted solution]

Hi @priscilarubim, 

Thank you for your help with this! My values are meant to be a drilldown, so I do need to adjust for millions and thousands. That did help, but I can't get that to work in a drilldown. I tried combining your measure with a bit of the logic from @Chanty4u and came up with this: 

If(
fabs(Sum({<ModelDiscretionaryAcctFlag={'N'}>} AccountValue)) >= 1000000000,

Num(
Sum({<ModelDiscretionaryAcctFlag={'N'}>} AccountValue) / 1000000000,
'$#,##0.0B;-$#,##0.0B'
),

If(
fabs(Sum({<ModelDiscretionaryAcctFlag={'N'}>} AccountValue)) >= 1000000,

Num(
Sum({<ModelDiscretionaryAcctFlag={'N'}>} AccountValue) / 1000000,
'$#,##0.0M;-$#,##0.0M'
),

If(
fabs(Sum({<ModelDiscretionaryAcctFlag={'N'}>} AccountValue)) >= 1000,

Num(
Sum({<ModelDiscretionaryAcctFlag={'N'}>} AccountValue) / 1000,
'$#,##0.0K;-$#,##0.0K'
),

Num(
Sum({<ModelDiscretionaryAcctFlag={'N'}>} AccountValue),
'$#,##0;-$#,##0'
)
)
)
)

BUT- that's changed the x-axis to values much lower than they should be for the top level. Any thoughts on fixing this?  

[priscilarubim · Accepted solution]

Hi @snightengale ,

I'm not sure I fully understood your point about the drilldown, but based on what I could see, I believe the issue is that dividing inside the measure expression itself distorts the x-axis positioning. When different values are divided by different factors (1B, 1M, 1K), Qlik plots them on the same axis at incompatible scales.

A possible fix is to use Dual(), which keeps the real value for axis positioning while only displaying the formatted abbreviation as the label:

Dual(
If(fabs(Sum({<ModelDiscretionaryAcctFlag={'N'}>} AccountValue)) >= 1000000000,
Num(Sum({<ModelDiscretionaryAcctFlag={'N'}>} AccountValue) / 1000000000, '$#,##0.0B;-$#,##0.0B'),
If(fabs(Sum({<ModelDiscretionaryAcctFlag={'N'}>} AccountValue)) >= 1000000,
Num(Sum({<ModelDiscretionaryAcctFlag={'N'}>} AccountValue) / 1000000, '$#,##0.0M;-$#,##0.0M'),
If(fabs(Sum({<ModelDiscretionaryAcctFlag={'N'}>} AccountValue)) >= 1000,
Num(Sum({<ModelDiscretionaryAcctFlag={'N'}>} AccountValue) / 1000, '$#,##0.0K;-$#,##0.0K'),
Num(Sum({<ModelDiscretionaryAcctFlag={'N'}>} AccountValue), '$#,##0;-$#,##0')))),
Sum({<ModelDiscretionaryAcctFlag={'N'}>} AccountValue)
)

I tested this on my end and the axis labels showed the correct abbreviations while the points stayed proportionally positioned. Hope this helps... let me know if I misunderstood the drilldown part!

[snightengale]

Thank you so much, @priscilarubim! The dual function was the missing key. I really appreciate your help with this! 

[priscilarubim]

@snightengale It was a pleasure to help 🙂