topic Qlikview: Negative values in Straight and Pivot table not totaling up in QlikView

Qlikview: Negative values in Straight and Pivot table not totaling up

AB108 — Fri, 19 Aug 2022 22:23:19 GMT

Hi,

I have an expression in a straight table

=Sum({$<Date={"<=$(vInputDate)"},CLEAR_DATE={0,">$(vInputDate)"}>} Value_$(vAB))

There are positive and negative values in this column.

I am trying to make two columns separating the positive and negative values.

The column with positive values are working perfectly but the one with neg values is not totaling, shows 0 for totals.

The same issue when i try separate Pivot tables for positive and neg.

Expressions I use for two columns:

Positive:

If(Sum({$<Date={"<=$(vInputDate)"},CLEAR_DATE={0,">$(vInputDate)"}>} Value_$(vAB))>0,

Sum({$<Date={"<=$(vInputDate)"},CLEAR_DATE={0,">$(vInputDate)"}>} Value_$(vAB)),0)

Negative:

If(Sum({$<Date={"<=$(vInputDate)"},CLEAR_DATE={0,">$(vInputDate)"}>} Value_$(vAB))<0,

Sum({$<Date={"<=$(vInputDate)"},CLEAR_DATE={0,">$(vInputDate)"}>} Value_$(vAB)),0)

I have already tried to make neg output as positive but no luck.

Re: Qlikview: Negative values in Straight and Pivot table not totaling up

BrunPierre — Sun, 21 Aug 2022 15:27:18 GMT

perhaps this

If(Sum({$<Date={"<=$(vInputDate)"},CLEAR_DATE={"0>$(vInputDate)"}>} Value_$(vAB))<0,

Sum({$<Date={"<=$(vInputDate)"},CLEAR_DATE={"0>$(vInputDate)"}>} Value_$(vAB)),0)

Re: Qlikview: Negative values in Straight and Pivot table not totaling up

BrunPierre — Thu, 25 Aug 2022 14:15:19 GMT

@AB108 Did it solve your problem?

Re: Qlikview: Negative values in Straight and Pivot table not totaling up

AB108 — Thu, 25 Aug 2022 15:11:53 GMT

Hi,

Using Aggr with the all the dimensions worked. I expected Qlik to internally group by the dimensions in St/Pivot tables but somehow it isnt working for subtotals/totals.

Positive:

Sum (
If (
Aggr(Sum({$<Date={"<=$(vInputDate)"},CLEAR_DATE={0,">$(vInputDate)"}>} Value_$(vAB)) , Dim1,Dim2)>0,
Aggr(Sum({$<Date={"<=$(vInputDate)"},CLEAR_DATE={0,">$(vInputDate)"}>} Value_$(vAB)) , Dim1,Dim2)
, 0))