topic Re: Convert Null to zero in Aggr when all the rows has been excluded by set expression in App Development

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

virilo_tejedor — Fri, 10 Aug 2018 12:07:41 GMT

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

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

sunny_talwar — Fri, 10 Aug 2018 12:11:59 GMT

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

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

niclaz79 — Fri, 10 Aug 2018 12:17:33 GMT

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

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

virilo_tejedor — Thu, 16 Aug 2018 11:41:45 GMT

Thanks a lot Sunny stalwar1‌ !!!

The problem is solved by adding this zero constant

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

virilo_tejedor — Thu, 16 Aug 2018 11:57:29 GMT

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? ¯\_(ツ)_/¯

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

danaleota1 — Wed, 21 Oct 2020 19:45:34 GMT

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)