I would like to do a sum of sales for the last 3 day and add as a calculated column. I’m not sure how to do this. Is it possible to exclude weekends in the calc?
It is a running sum for 3 days, or just a sum of the last 3 days (i.e. ignoring everything 4 days and later)?
To exclude weekends you can use the weekday() function and the “Filter” transformation.
Just a sum for the last 3 days excluding weekends. I would like to create an additional column that is able to do this.
Date | Client | Product | Sales | 3 Days Sum
12-03-2018 | MG | Car | 100 | 114 (Excludes weekends)
11-03-2018 | MG | Car | 70 | 202 (carried value from Friday)- It’s ok if this is empty
10-03-2018 | MG | Car | 130 | 202
09-03-2018 | MG | Car | 12 | 202
08-03-2018 | MG | Car | 90 | 122
07-03-2018 | MG | Car | 12 |
06-03-2018 | MG | Car | 100 |
05-03-2018 | MG | Car | 12 |
In EasyMorph a sliding sum for the last N days is calculated as running total minus the running total shifted by N days. The math behind it:
(x1 + x2 + x3 + x4 + x5 + … xN) - (x4 + x5 + … xN) = (x1 + x2 + x3)
Therefore, to calculate a sliding sum for 3 workdays, we need to exclude weekends, calculate a running total, then subtract from it the same running total but shifted by 3 days.
See the example below that calculates a sliding sum of sales. Note that for the running totals to calculate correctly, the dates should be in ascending order. The last Fill Down transformation is optional – enable it if you need to full weekends with Monday 3-day sales numbers.
Sales last 3 workdays.morph (7.3 KB)
What if there is more than one customer in the table and I need to group the running total by customer? I know I can group the running total but I don’t think shifting the cells down 3 spaces would be effective.
Yes, shifting happens for entire column, not in groups. It may (or may not) be managed by shifting customer name together with running total, and not using the running total if customer names don’t match.
Alternatively, the original dataset can be partitioned by customer and processed in iterations.