Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
vinay_bangari
Valued Contributor III

In Pivot table, How to fabs first and then sum

Hi,     I’m trying to get the final result as sum of total for the +ve and –ve values. Please help.2016-03-22_10-23-18.pngsunindia

Tags (1)
1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Re: In Pivot table, How to fabs first and then sum

Maybe like

If(Dimensionality()<2,

Sum(Aggr(

If(SUM( [TOTAL EXPENSES])>0,SUM( [TOTAL EXPENSES]))

,MARKET,Risk_Type))

,

SUM([TOTAL EXPENSES]))

View solution in original post

12 Replies
Highlighted

Re: In Pivot table, How to fabs first and then sum

If you make it positive, I get a little different number using this:

If(Dimensionality() = 2, SUM([TOTAL EXPENSES]), Sum(fabs([TOTAL EXPENSES])))


Capture.PNG

Highlighted

Re: In Pivot table, How to fabs first and then sum

The answers matches your if we completely exclude negative from the calculation

If(Dimensionality() = 2, SUM([TOTAL EXPENSES]), Sum({<[TOTAL EXPENSES] = {'>0'}>}[TOTAL EXPENSES]))


Capture.PNG

Highlighted
ramasaisaksoft
Valued Contributor III

Re: Hi, I’m trying to get the final result as sum of total for the +ve and –ve values. Please help.

Hi Kumar

check the link -ve to +ve convertion

*** Negative Value to Positive Value ***

Highlighted
MVP
MVP

Re: In Pivot table, How to fabs first and then sum

If you are trying to show the sum-of-rows (with absolute values) for the partial sums, but still also negative values for the details, I would suggest that you create a field in your data model

LOAD

     ...

     if( left([VENDOR ID],3)= 'CPM' and Risk_Type = 'RISK', 'CAC_RISK', Risk_Type) as New_Risk_Type,

     ...

Then in your chart, use this new field as dimension and as expressions

=If(Dimensionality()<2,

Sum(Aggr(

Fabs(SUM( [TOTAL EXPENSES]))

,MARKET,New_Risk_Type))

,

SUM([TOTAL EXPENSES]))

Highlighted
vinay_bangari
Valued Contributor III

Re: In Pivot table, How to fabs first and then sum

‌Bro.. Can you check the total for orlando. It should be 73,743,387 when we exclude the negative value completely...

Highlighted
vinay_bangari
Valued Contributor III

Re: In Pivot table, How to fabs first and then sum

‌Bro.. Can you check the total for orlando. It should be 73,743,387 when we exclude the negative value completely... Please help...

Highlighted
vinay_bangari
Valued Contributor III

Re: In Pivot table, How to fabs first and then sum

hi swehul,

s‌till I'm getting the wrong total.

Highlighted
MVP
MVP

Re: In Pivot table, How to fabs first and then sum

Could you upload the changed QVW with the new dimension and could you also detail what you expect to see, i.e. which lines should show which values and how you derive the values from your original table?

Highlighted
vinay_bangari
Valued Contributor III

Re: In Pivot table, How to fabs first and then sum

‌As per my requirement, it should exclude the negative value from the total.