# Qlik Sense App Development

cancel
Showing results for
Did you mean:
Highlighted Contributor III

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

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

1 Solution

Accepted Solutions
Highlighted MVP

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

4 Replies
Highlighted MVP

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

Highlighted Partner

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

Highlighted Contributor III

Thanks a lot Sunny stalwar1‌ !!!

The problem is solved by adding this zero constant

Highlighted Contributor III

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