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

[AB108]

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.

[BrunPierre]

perhaps this

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

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

[BrunPierre]

@AB108 Did it solve your problem?

[AB108 · Accepted solution]

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