Formatting Negative Numbers

[qlikbobby]

The solution given by marcowedel‌‌ (Kudos to you Master!) in Formatting numbers with VBscript function thread working perfectly for positive numbers, but in my case I've mixed positive and negative numbers. Is there any way we can show both positive and negative numbers formatted in 'B','M','K' and '$'?

[tamilarasu · Accepted solution]

Hi Sasi,

If you want to show negative value as it is and format as 'B','M','K', '$ then simply change the below line,

SET vSize = Div(Log10(Fabs($1)),3);

If you want to convert all the negative values to postive and then display the format as 'B','M','K', '$ then simply change this line,

LOAD *,

$(vFormatNumber(Fabs(parameter))) as paramFormat;

[qlikbobby]

Thanks Arasu, I should have mentioned it earlier but my bad I changed my expression something similar to this Dual('$'&Num(Sum(Sales)/Pow(10,3*Div(Log10(Sum(Sales)),3)),'#,##0.0')&' '&Pick(Div(Log10(Sum(Sales)),3),'K','M','B','T'),Sum(Sales)).

[tamilarasu]

Hi Sasi,

Sorry for the delay in response. I was off yesterday.

Try this,

SET vFormatNumber = Dual('$'&Num(Sum(Sales)/Pow(10,3*Div(Log10(Fabs(Sum(Sales))),3)),'#,##0.0')&' '&Pick(Div(Log10(Fabs(Sum(Sales))),3),'K','M','B','T'),Sum(Sales));