Skip to main content
Announcements
Introducing Qlik Answers: A plug-and-play, Generative AI powered RAG solution. READ ALL ABOUT IT!
cancel
Showing results for
Search instead for
Did you mean:
Creator

## 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]
[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
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))
)

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

Partner - Creator III

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

Creator
Author

Thanks a lot Sunny stalwar1‌ !!!

The problem is solved by adding this zero constant

Creator
Author

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

Creator

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)