topic Formatting Negative Numbers in QlikView

Formatting Negative Numbers

qlikbobby — Wed, 25 Nov 2020 16:16:04 GMT

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 '$'?

Re: Formatting Negative Numbers

tamilarasu — Wed, 06 Jun 2018 03:18:33 GMT

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;

Re: Formatting Negative Numbers

qlikbobby — Wed, 06 Jun 2018 18:44:52 GMT

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)).

Re: Formatting Negative Numbers

tamilarasu — Fri, 08 Jun 2018 06:40:12 GMT

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));