Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
qlikbobby
New Contributor

Formatting Negative Numbers

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

1 Solution

Accepted Solutions

Re: Formatting Negative Numbers

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

Capture.PNG

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;

Capture.PNG

View solution in original post

3 Replies

Re: Formatting Negative Numbers

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

Capture.PNG

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;

Capture.PNG

View solution in original post

qlikbobby
New Contributor

Re: Formatting Negative Numbers

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

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