Using the rangesum function to get the last 3 days sum for each employee

[MassicotPSCU]

Hi,

I want to get the last 3 days running total for each employee using the range sum function in the script. I have this table below:

Raw Data:

Employee ID	Date	Amount
A	1/1/2024	20
A	1/2/2024	5
A	1/3/2024	15
A	1/4/2024	10
A	1/5/2024	15
A	1/6/2024	10
B	1/1/2024	20

I am expecting this table below:

Expected Result:

Employee ID	Date	Amount	Running Total
A	1/1/2024	20	20
A	1/2/2024	5	25
A	1/3/2024	15	40
A	1/4/2024	10	30
A	1/5/2024	15	40
A	1/6/2024	10	35
B	1/1/2024	20	20

Currently, the problem with my code in the load script is that it gives the running total for each employee but not the last 3 days, it gives a running total of everything. So, for example, on 1/6/2024, Employee ID A will have 75 when the expected result is supposed to be 35. Here's my code:

Sales: 
Load "Employee ID",Date,
Sum(Amount) as Amount //2. Sum the amount.
FROM [Sales.qvd]
(qvd);
GROUP BY "Employee ID",Date; //3. Group by EID and Date

Sales_RT:
Load //4. Load everything from the sales table
*,
if(peek("Employee ID")= "Employee ID",
rangesum(peek("Amount Running Total"),"Amount"),"Amount") as "Amount Running Total" //5. Create a running total if the previous EID = the current EID
Resident Sales
Order by "Employee ID","Date" asc; // 6.Order by EID and Date.

[henrikalmen]

Try adding a date filter to the Sales_RT load so that you only load data for the three last days. For example something like WHERE Date<=today()-2(but it depends on what data you actually have and what criteria you must use).

After that - if you need it - you concatenate the table with a similar load statement but with null() as "Amount Running Total" and the date filter should now give you only the rows that represent day 4 and higher.

[MassicotPSCU]

Hi I get what you're saying but this solution isn't exactly what I need. I still need to load all the data, I just want to do a running sum of the data, grouped by employee id and in 3-day windows. Where the window goes from 3 days/rows preceding to the current row.

[Chanty4u]

Try this

RangeSum(Above(TOTAL Sum({<Date={"$(=Date(Max(Date) - 2))"}>} Amount), 0, RowNo()))

[Anil_Babu_Samineni]

@MassicotPSCU Can you please explain the calculation what did you done for this to get "Running Total", Based on that we will see what script is suitable for you. 

[vinieme12 · Accepted solution]

As below

temp:
  LOAD EmployeeID,Date(Date#(Date,'D/M/YYYY')) as Date,Amount inline [
EmployeeID,Date,Amount
A,1/1/2024,20
A,1/2/2024,5
A,1/3/2024,15
A,1/4/2024,10
A,1/5/2024,15
A,1/6/2024,10
B,1/1/2024,20
];

Main:
Load *
,if(EmployeeID=Peek(EmployeeID),Rangesum(Amount,if(recno()>1,Peek(Amount,Recno()-2)),if(recno()>2,Peek(Amount,Recno()-3))),Amount) as Cumulative
Resident temp
ORder by EmployeeID,Date asc;
drop table temp;
exit Script;

[MassicotPSCU]

Hi, this works but before I mark it as a solution, is there a way to do this with some sort of looping construct?

The reason I ask is because what I provided was just an example of my data. For my real data, I need to get the running total for the last 182 days instead of just 3. That recno() code was really clever but, writing that code 182 times is going to be a real pain, so I'm looking for something more concise. 

[MassicotPSCU]

Hi @vinieme12 , instead of going back 182 days, my manager advised that I could just group the sum by month and just look at the past 6 months. With that, all I had to do was write 5 lines of the if recno logic. Thanks a lot for your help!