topic Summing the total positive values from a column in QlikView

Summing the total positive values from a column

ryanfoster84 — Thu, 18 May 2017 20:41:52 GMT

hi I am looking assistance with getting the sum of all the positive number from a column within a straight table in Qlikview but am running in trouble. I am attaching a spread sheet with the straight table in and you can reference it.

basically the table has 5 columns (A-E in the spread sheet) the issue I am having is that I want the sum of all the positive numbers of column e in the spread sheet (90.07).

I will then create a new column (column h) which divides column 6 / column 7 giving me the new column e.

I have tried numerous different things with no luck.

the formula that makes up column e (just for background info) is a follows

=IF($(vYoY_Chart) >0,($(vYoY_Chart))/($(vGrossPositiveVolumeYoY_Chart)),($(vYoY))/(FABS($(vGrossNegativeVolumeYoY_Chart))))

and the variables within this are broken out as follows:

=================================================================
vYoY_Chart
=================================================================
(Sum(
{$<
[Month] = ,
[Year] = {$(vMaxYear)}
>}
Volume))
-
(Sum(
{$<
[Month] = ,
[Year] = {$(vPreviousYear)}
>}
Volume))

=================================================================
vGrossPositiveVolumeYoY_Chart
=================================================================

=(SUM(Aggr(IF($(vYoY_Chart)>=0 AND [FO Indicator] = 'O',$(vYoY_Chart),0),[Product Line],[Product Description])))
+
(SUM(Aggr(IF($(vYoY_Chart)>=0 AND [FO Indicator] = 'F',$(vYoY_Chart),0),[Product Line],[Product Description])))

=================================================================
vGrossNegativeVolumeYoY_Chart
=================================================================

=(SUM(Aggr(IF($(vYoY_Chart)<0 AND [FO Indicator] = 'O',$(vYoY_Chart),0),[Product Line],[Product Description])))
+
(SUM(Aggr(IF($(vYoY_Chart)<0 AND [FO Indicator] = 'F',$(vYoY_Chart),0),[Product Line],[Product Description])))

Re: Summing the total positive values from a column

Anonymous — Thu, 18 May 2017 21:15:10 GMT

So is column e not calculating? Try

=IF($(vYoY_Chart) >0,($(vYoY_Chart),0)/($(vGrossPositiveVolumeYoY_Chart)),($(vYoY))/(FABS($(vGrossNegativeVolumeYoY_Chart))))

Or are your variables not calculating correctly?

Re: Summing the total positive values from a column

ryanfoster84 — Thu, 18 May 2017 21:19:45 GMT

Column E is calculating but I am trying to get the total positive sum from column e in another column. so it should be 90.07

Re: Summing the total positive values from a column

Anonymous — Thu, 18 May 2017 21:27:10 GMT

Maybe

=sum(TOTAL rangemax([Weighted % of Gain/Loss YoY],0))

Or am I missing something?

Re: Summing the total positive values from a column

ryanfoster84 — Fri, 19 May 2017 14:03:31 GMT

hi no this does not work sadly. so just to make things easier I need to get the total positive volume for column e (90.07) in the spreadsheet. the formula that makes column is listed above.

Re: Summing the total positive values from a column

ryanfoster84 — Fri, 19 May 2017 15:52:13 GMT

I solved it

=sum(Total aggr(IF($(vYoY_Chart) >0,($(vYoY_Chart))/($(vGrossPositiveVolumeYoY_Chart)),0),[Description],[Name]))