Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

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
Highlighted

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
Highlighted

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

Highlighted
Contributor II
Contributor II

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

Highlighted

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