Replace Null with Before in cumulative pivot table

[rlawton1]

I have a pivot table that shows cumulative values across the horizontal.

The formula for the measure is:

RangeSum(Before(Alt(Sum(TOTAL_INCURRED),0), 0, ColumnNo()))

If there is no total incurred for a given month then it is null (circled in red). How can I get it to display the value before rather than null so they are no gaps.

pivot

I have tried the following formula:

IF(IsNull(RangeSum(Before(Alt(Sum(TOTAL_INCURRED),0), 0, ColumnNo()))),Before(Alt(Sum(TOTAL_INCURRED),0),0),RangeSum(Before(Alt(Sum(TOTAL_INCURRED),0), 0, ColumnNo())))

IF(IsNull(RangeSum(Before(Alt(Sum(TOTAL_INCURRED),0), 0, ColumnNo()))),RangeSum(Before(Alt(Sum(TOTAL_INCURRED),0), 0, ColumnNo()-1)),RangeSum(Before(Alt(Sum(TOTAL_INCURRED),0), 0, ColumnNo())))

And I have tried using IF(len()=0  rather than IF(IsNull, but whatever I try I get the same result.

Can anyone suggest how to achieve this?

Thanks

[brunobertels]

Hi 

not tested but try this : 

RangeSum(Before(Alt(Sum(TOTAL_INCURRED) + Sum ({0} 0),0), 0, ColumnNo()))

Not sure it works but a remember to have read the same kind of problem and it was solved with adding somethink like this  + Sum ({0} 0)so that it's avoid null value and continue the cumulative sum 

hope it helps 

[rlawton1]

I've seen that elsewhere but its not working for me, no change to the pivot table

[brunobertels]

Hi 

Waht about in that case to generate 0 value for all your dimension combination : 

https://community.qlik.com/t5/New-to-Qlik-Sense/Summing-Nulls-in-a-Cumulative-Count/td-p/1754264 

: 

"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."

[rlawton1 · Accepted solution]

I have found a solutionto this for anyone having hte sae problem.

I create as AsOfTable. First I create a master calendar that links to the date used as the horizontal dimension. And then by using the AsOfMonth as the pivot table dimensions, I only need t use SUM(TOTA_INCURREd0 as the measure to get the desired result.

MasterCalendar:
Load
TempDate AS DECLARATION_DATE,
TempDate,
week(TempDate) As Week,
Year(TempDate) As Year,
//Month(TempDate) As Month,
Date(MonthStart(TempDate),'YYYY-MM') as Month,
Day(TempDate) As Day,
YeartoDate(TempDate)*-1 as CurYTDFlag,
YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;

// ======== Create a list of distinct Months ========
tmpAsOfCalendar:
Load distinct Month
Resident MasterCalendar;

// ======== Cartesian product with itself ========
Join (tmpAsOfCalendar)
Load Month as AsOfMonth
Resident tmpAsOfCalendar;

// ======== Reload, filter and calculate additional fields ========
[As-Of Calendar]:
Load Month,
AsOfMonth,
Round((AsOfMonth-Month)*12/365.2425) as MonthDiff,
Year(AsOfMonth)-Year(Month) as YearDiff
Resident tmpAsOfCalendar
Where AsOfMonth >= Month;

Drop Table tmpAsOfCalendar;