Summing Nulls in a Cumulative Count

[danaleota1]

Hello,

I am using rangesum() to get acccumlative count for a percentage and the following expression is working for a pivot table:

=RangeSum(Before(Sum({< IsCurrentMTD = {'1'} >} [SL1]), 0, ColumnNo()))/
RangeSum(Before(Sum({< IsCurrentMTD = {'1'} >} INTSANS), 0, ColumnNo()))

*don't want nulls see column 10/2/2020 first row.

However I am getting nulls for when there is no data instead of the previous value which is what I want.  I tried the following based on similar question that was asked but this did not work:    

=AGGR(RangeSum(Before(Sum({< IsCurrentMTD = {'1'} >} [SL1]) + sum({1} 0), 0, ColumnNo())),Date)/
AGGR(RangeSum(Before(Sum({< IsCurrentMTD = {'1'} >} INTSANS) + sum({1} 0), 0, ColumnNo())), Date)

Any help to remove nulls and get the previous day's cummulative count instead.  Thank you.  

[Oleg_Troyansky · Accepted solution]

Hi there!

Yes, this is a known problem, and unfortunately AFAIK there is no elegant solution to it. The problem that these cells don't even hold NULL values. These are simply missing combinations of your two dimensions . The value is simply not there, and therefore the cell is not filled with anything, hence it can't participate in the accumulated calculation.

The only work around for this situation is to generate dummy records in your data with zero values for all possible combinations of the two dimensions. It will increase the size of your database table, but this is the only way (known to me) to fill in the missing cells.

Best,

[danaleota1]

Hi Oleg,

I used the following script to populate dummy records and it is now populating the previous nulls with the previous days sums in my pivot table where I was using rangesum() to get an accumulative count. Which is what I wanted.  Thank you. 

[SLATABLE]:
Load
Date,
INSTITUTIONAME,
INTSANS,
SL1,
SLA
Resident SLA;

Drop Table SLA;

Temp:
Load Distinct INSTITUTIONAME
Resident [SLATABLE];

Join (Temp)

Load Distinct Date
Resident [SLATABLE];

AccountManager:
Load INSTITUTIONAME & '-'& Date as Key,
INSTITUTIONAME ,
Date
Resident Temp;

Drop table Temp;

Left join(AccountManager)
Load *,INSTITUTIONAME & '-'& Date as Key
Resident SLATABLE;