Cumulative total calculation not working with pivot table

[david145]

hi,

im using a cumulative calculation, 

if i put this measure into a table, it works fine. However when i put it into a pivot table with a field in the row dimension, it doesnt take this into account and the results are incorrect.

my formula is:

rangesum(above(total count(distinct [Demand_ID]) - count(distinct [%ID]), 0, rowno(Total)))

as you can see the table is correctly giving me the cumulative total, however when i put the same formula into a pivot table, its giving me the same results as the "difference" column.

the field i use for my row dimenson is "Area". do i need to put this field into my formula above so it takes this into account?

each ID is assigned to one of the values in the "Area" field so i want to see the cumulative variance for each area over the weeks.

thanks for any ideas!

[Or]

Not sure if I'm reading this correctly, but if I am, it seems you're looking for the Before() function, rather than Above():

https://help.qlik.com/en-US/sense/May2022/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/InterRecor...

[david145]

hi @Or  thank you for your reply.

if i replace above() with before(), im still getting the same results. 

ive attached some sample data for reference. i filtered it to one "area" but we can assume there are multiple areas.

the 3rd column is my measure where i want the cumulative total to start

let me know what you think,

thank you

[Or · Accepted solution]

You should be looking at something along the lines of:

RangeSum(Before( Sum(Data),0,ColumnNo()))

Load * INLINE [
Week, Area, Data
1, A, 0
2, A, 2
3, A, 1
1, B, 5
2, B, 0
3, B, 1
];