We have a pivot table with a date dimension along the top.
We have a row which is populated by a 1 or a 0 by comparing the sum of the service level (either a 1 or 0) to the number of working days in order to only count those working days which are 1 and where there was not a previous value of 0. We want to then find the sum of these 1s and put this in our total column.
The calculation we are using for the row is:
if(weekdayRMflag=0,0,if(Rangesum(Before(Sum(ServiceLevel),0,NoOfColumns()))=Rangesum(Before(Sum(weekdayRMflag),0,NoOfColumns())),1,0))
In order to make the total work (just using the pivot table's own total doesnt work) I;ve tried to use:
=if(ColumnNo()=0,
Sum(Aggr(after(rangesum(if(weekdayRMflag=0,0,if(Rangesum(Before(Sum(ServiceLevel),0,NoOfColumns()))=Rangesum(Before(Sum(weekdayRMflag),0,NoOfColumns())),1,0))),1,NoOfColumns()), planningdiaryname, ECEngID, ECEng, ECEngType))
,
if(weekdayRMflag=0,0,if(Rangesum(Before(Sum(ServiceLevel),0,NoOfColumns()))=Rangesum(Before(Sum(weekdayRMflag),0,NoOfColumns())),1,0))
)
in order to make it do something different with the total row which is the first column but this is not working.
