Cumulative sum for two (or more) dimensions

[oddgeir]

In QlikSense load script, how do i do cumulative sum for several dimensions?

Given the following input data (regarding planned changes in capacity)

StaffChanges:
  Load * inline [
    Period, Department, Change, Text
    202401, Dept1, 100, Baseline
    202402, Dept2, 100, Baseline
    202401, Dept3, 100, Baseline
    202403, Dept1, 10, Staff change
    202404, Dept2, -15, Staff change
    202404, Dept3, 25, Staff change
    202407, Dept3, -10, Staff change
    202401, Dept2, 50, Staff change
  ];

How do I create a new table, showing the current staff in each department, by month?

I want to do this in load script to ensure the data is fixed. 

[LorantPataki · Accepted solution]

Hi there,

try this:

StaffChanges:

  Load * inline [

    Period, Department, Change, Text

    202401, Dept1, 100, Baseline

    202402, Dept2, 100, Baseline

    202401, Dept3, 100, Baseline

    202403, Dept1, 10, Staff change

    202404, Dept2, -15, Staff change

    202404, Dept3, 25, Staff change

    202407, Dept3, -10, Staff change

    202401, Dept2, 50, Staff change

  ];

CurrentStaff:

NoConcatenate Load

Department,

    Period,

    If(Department = Previous(Department), Peek(CurrentStaff) + Change, Change) as CurrentStaff

Resident StaffChanges

Order By Department, Period;

Drop Table StaffChanges;

And the results are:

My only remark is that the oldest month for each department should contain the "Baseline", which is not the case for Dept2.

[oddgeir]

Ahhh... thank you. 

Yes this seems to be exactly what I was looking for, but just couldn't wrap my head around at the moment. 

Sorry for poor data quality in my dummy data. I was just making a few rows quckly and didn't capture everything. 

[LorantPataki]

Glad it worked 🙂

Cheers