Hi, I am currently developing a dashboard and occasionally, the values in the charts are fairly big like in the millions. The problem is that the values in millions will be displayed as something like '2e+7' or something like that if I set the formatting to 'Auto' and if I set the formatting to 'Money' or 'Number', it will show the values in full instead which took up a lot of spaces in the chart. I tried formatting the values in the hardcoded way like 'NUM(SUM(value)/1000000, '$#,###.##M')', while it worked temporarily but it really doesn't scale well in other scenarios as the smaller values will be shown as something like '$0.001M'. I tried formatting the numbers with IFs function like:
IF(SUM(value) >= 1000000, NUM(SUM(value)/1000000, '$#,###.##M'), IF(SUM(value) >= 1000, NUM(SUM(value)/1000, '$#,###.##'))
The problem with the expression above is that some charts will recognize the values that are supposedly in millions to be smaller than other values as the values in millions are divided by a million for formatting purpose like 12.5M < 1250. I checked the script and the Numerical Abbreviation is properly set that 6 figures numbers will be shown in M symbol but it showed me in e-notation instead. It's such a shame as these things are handled easily and well in QlikView while seems like not the case for Qlik Sense. Is there a way to configure these number formatting so that it is scalable in most scenario and not in the hardcoded way? Appreciate all the help!
