Automatically select thousands, millions, or billions for a chart

[BrandonF]

In looking through the forums, I've found the following solution for automatically formatting based on the value:

IF($(=REPLACE(FABS($1),";",","))>1000000000,
               num(
                              $(=REPLACE($1,";",","))/1000000000,'$(vCurrencyFormat)'&'.00B'
    ),
    IF($(=REPLACE(FABS($1),";",","))>1000000,
               num(
                                             $(=REPLACE($1,";",","))/1000000,'$(vCurrencyFormat)'&'.00M'
               ),
        IF($(=REPLACE(FABS($1),";",","))>1000,
               num(
                                                            $(=REPLACE($1,";",","))/1000,'$(vCurrencyFormat)'&'.0K'
                              ),
            IF($(=REPLACE(FABS($1),";",","))<10,
               num(
                                                                           $(=REPLACE($1,";",",")),'$(vCurrencyFormat)'&'.000'
                                             ),
                                                            num(
                                                                           $(=REPLACE($1,";",",")),vCurrencyFormat
                                             )
            )
        )
    )
)

This has worked wonderfully for KPIs and other single-number use cases. However, if I try to use this in a bar graph, where some of the values are in thousands and some are in millions or billions, it causes some chaos. Each individual value is evaluated and then you have a situation where 500K appears to be larger than 1M or even 1B. 

Obviously this is a huge problem. It makes the chart useless. Is there a way to apply the formatting ONLY to the displayed value and not the value the chart is using? so the chart is using 1,000,000,000 as the value for the bar but 1B for the value of the label? That way the relative height of the bars would always be correct while the values displayed would also be easily read.

[Gabriel]

Hi,

Read this blog to see if it helps you.

https://qlikviewcookbook.com/2016/05/scaling-numbers-and-dse-tips/

[BrandonF]

That is essentially the method that I'm using.

The issue comes in when you divide anything over a million by 1 million and then thousands by 1 thousand.

1,524,679 / 1E6 = 1.5

4,679 / 1E3 = 4.7

1,524,679 > 4,679  but 4.7 > 1.5

Thus, when you put them on a graph, the 4,679 appears to be a larger value than 1,524,679. If it were possible to ONLY affect the label applied to the graph without the underlying value, that would solve the issue. As soon as you divide the original values, however, you are potentially changing the comparison being made.

[Kushal_Chawda · Accepted solution]

why don't you try dual function. check out below

https://community.qlik.com/t5/QlikView-Documents/How-to-use-Dual/ta-p/1477054

In your case expression would look like

=dual ( ConversionExpression , ActualExpression)

Where ConversionExpression is your expression which converts number to specific format like B, M &K

[BrandonF]

---

@Kushal_Chawda wrote:

why don't you try dual function. check out below

https://community.qlik.com/t5/QlikView-Documents/How-to-use-Dual/ta-p/1477054

In your case expression would look like

=dual ( ConversionExpression , ActualExpression)

Where ConversionExpression is your expression which converts number to specific format like B, M &K

---

Beautiful, you've solved my issue. I was tearing my hair out on this one. Thank you for your help!
A note for anyone in the future using this: In addition, you also need to set the number formatting to 'Measure Expression' for it to display properly-otherwise, you'll just get a normal number.

[Kushal_Chawda]

that's the good point. totally forgot. but good that you figured it out your own