Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
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:

failure-when-100%-errors.png

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:

failure-when-100%-errors-new-data.png

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
Highlighted

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

View solution in original post

4 Replies
Highlighted

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

View solution in original post

Highlighted
Partner
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
Contributor III

Thanks a lot Sunny stalwar1‌ !!!

The problem is solved by adding this zero constant

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