Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
LauraMorris
Contributor II
Contributor II

Pivot table sum of column involving before and rangesum

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.

 

Labels (3)
0 Replies