Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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))
perhaps this
If(Sum({$<Date={"<=$(vInputDate)"},CLEAR_DATE={"0>$(vInputDate)"}>} Value_$(vAB))<0,
Sum({$<Date={"<=$(vInputDate)"},CLEAR_DATE={"0>$(vInputDate)"}>} Value_$(vAB)),0)
@AB108 Did it solve your problem?
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))