Hi @Kemuel ,
These are the nodes that I added to your workflow:
The Table Creator simply contains 12 rows, with values from 1 to 12 (each month number)
I added the Extract Date&Time Fields in order to be able to separate the months (and year) so that it’s the same as what you showed in your original screenshot - it’s needed to add the missing months.
So, in the Group Loop Start, I also added the Year as a criteria:
The first iteration of the Loop starts with this:
When I run the Left Join, I get this:
So we can see that we now have the rows for all 12 months.
After we run the 2 Lag Columns node, we can see the correct result for month 6 and 8 for Qty(-3) and Qty(-4):
We then run an inner join back with the original dataset that had only months 6 and 8, so the inner join would return the rows only for months 6 and 8:
And here’s the final results:
Here’s the workflow: Sample Data_Bruno.knwf (31.0 KB)
EDIT: Just to add a note: These take care of the gaps as you mentioned gaps. But looking at your dataset, you also have the contrary, that is you have duplicate months for the same year and same ID. So this creates the reverse issue, which was not part of the question
If that is an issue, then you need to define what needs to be done in these cases. One option is to probably do a groupby first and aggregate the Qty per date.
Here’s how I’m addressing this issue by doing the aggregation. I’m replacing the sort node by a groupby, and then moving the sorter after the join:
Here’s the new result:
Here’s the updated workflow: Sample Data_Bruno.knwf (32.9 KB)