Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
rlawton1
Contributor III
Contributor III

Replace Null with Before in cumulative pivot table

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.

pivotpivot

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

 

Labels (3)
1 Solution

Accepted Solutions
rlawton1
Contributor III
Contributor III
Author

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;

View solution in original post

4 Replies
brunobertels
Master
Master

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
Contributor III
Contributor III
Author

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

brunobertels
Master
Master

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
Contributor III
Contributor III
Author

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;