topic Cumulative sum for two (or more) dimensions in App Development

Cumulative sum for two (or more) dimensions

oddgeir — Wed, 05 Jun 2024 16:19:12 GMT

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.

Re: Cumulative sum for two (or more) dimensions

LorantPataki — Fri, 07 Jun 2024 09:29:04 GMT

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.

Re: Cumulative sum for two (or more) dimensions

oddgeir — Fri, 07 Jun 2024 09:58:39 GMT

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.

Re: Cumulative sum for two (or more) dimensions

LorantPataki — Fri, 07 Jun 2024 10:01:57 GMT

Glad it worked 🙂

Cheers