Convert Null to zero in Aggr when all the rows has been excluded by set expression

[virilo_tejedor]

Hi,

I'm using this expression to calculate the cumulative sum:

RangeSum(
Aggr(
  Rangesum(
   above( Sum({$<ERROR={0},BALANCE_GROUP={*}-{1}>}[BALANCE_FACTOR]),
            0,
            vMaxDays

      )
  )
  , BALANCE_GROUP_DESC , (FEC_PETICION.autoCalendar.Date, (NUMERIC))
)
)

These are the expected value and what I'm getting:

I think I've a problem, since I'm excluing rows where ERROR<>0 and grouping by dates.

nd when all the rows from the same date are excluded by the set expression.

I think QlikSense is creating a row in Agg for 10/3/2010 (since there is data);
but has nothing to sum because al the rows has been excluded:

I tried configuring the chart with Additional Modules > Suppress ¿null/zero? values? (It's translated into Spanish as "Suprimir valores cero")
It does the job for 10/3/2010 (null), but also supress real zeroes.

Is there any way to add a constant zero to the list of values being aggregated by Aggr?
Something like [0 + above(...)]:

    [0] --> [0+0]
    [0+2] --> [0+0+2]
    [0+2+1] --> [0+0+2+1]
    [0+2+1+3] --> [0+0+2+1+3]
    [0+2+1+3-1] --> [0+0+2+1+3-1]
          ...
    [Null]  --> [0+Null]

           ...

How could I help QlikSense to realize that there is no change for cumulative sum in 10/3/2010?

TIA.

Best regards,

Virilo

[sunny_talwar · Accepted solution]

Try this

Aggr(
  Rangesum(
  Above(Sum({$<ERROR={0},BALANCE_GROUP={*}-{1}>}[BALANCE_FACTOR]) + Sum({1} 0),
            0,
            vMaxDays

      )
  )
  , BALANCE_GROUP_DESC , (FEC_PETICION.autoCalendar.Date, (NUMERIC))
)

[niclaz79]

Hi,

I prefer to use the Alt() function when dealing with undesired null values;

Aggr(
  Rangesum(
  Above(Sum({$<ERROR={0},BALANCE_GROUP={*}-{1}>}Alt([BALANCE_FACTOR],0))
            0,
            vMaxDays

      )
  )
  , BALANCE_GROUP_DESC , (FEC_PETICION.autoCalendar.Date, (NUMERIC))
)

[virilo_tejedor]

Thanks a lot Sunny stalwar1‌ !!!

The problem is solved by adding this zero constant

[virilo_tejedor]

Hi Niclas!

Thanks a lot for your response.

I tried it without success. I think it's perhaps due to a little bug in Aggr. Could be that Aggr is creating a row for 10/3/2010 (since there is data); but has nothing to sum because al the rows for 10/3/2010 been excluded by the 'SetExpression' (there are no rows this day satisfaying ERROR={0}); and this problem would escape to Alt scope being executed during the 'expr' evaluation. Could be? ¯\_(ツ)_/¯

[danaleota1]

Hi Sunny I'm trying to do something similar with a pivot table.  I have the following expression that works except for when there is no data.  

=RangeSum(Before(Sum({< IsCurrentMTD = {'1'} >} [SL1]), 0, ColumnNo()))/
RangeSum(Before(Sum({< IsCurrentMTD = {'1'} >} INTANS), 0, ColumnNo()))

I tried to add the sum({1} 0)  but I'm not getting the desired result 

=AGGR(RangeSum(Before(Sum({< IsCurrentMTD = {'1'} >} [SL1]) + sum({1} 0), 0, ColumnNo()))/
RangeSum(Before(Sum({< IsCurrentMTD = {'1'} >} INTANS) +sum({1} 0), 0, ColumnNo())), Date)