Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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))
)
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))
)
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))
)
Thanks a lot Sunny stalwar1 !!!
The problem is solved by adding this zero constant
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? ¯\_(ツ)_/¯
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)