sum upto last n dates data

[PKN]

Hi all,

I am new to the Alteryx and searching for help to solve the following problem.

The sample data looks like:

item	date	Qty	Sum_Qty_21_days
A	27/11/2019	5	12
B	22/11/2019	3	4
D	29/10/2019	2	3
E	10/11/2019	1	1
F	25/11/2019	2	7
C	25/11/2019	1	7

The column Sum_Qty_21_days should contains the sum of Qty of last 21 days. e.g. Sum_Qty_21_days field for date 27/11 have value 12 because of sum of column Qty for dates 27/11, 22/11, 10/11 and 25/11 (for both item F and C). Similarly, Sum_Qty_21_days field for date 22/11 have value 4 because of sum of column Qty for dates 22/11 and10/11.

Note: Same date can have multiple rows, sum should consider multiple dates too.

First priority is to perform this with In-database tools only, if not possible, solution with other tools are welcome too. 

Thanks in advance.

[OllieClarke · Accepted solution]

Hi @PKN This workflow does what you want not in-db. My output is slightly different to yours, but I believe it is correct. I'm not an expert in in-db, so I don't know how you would do the generate rows part of the workflow in-db. If you can work that out, then all the other steps are simple in-db.

Hope that helps,

Ollie

[Dazzerman · Accepted solution]

Hi @PKN 

I see that @OllieClarke beat me to it!

I have attached how I achieved a solution to your problem.

I hope this helps.

[PKN]

Thanks a lot for the solution @Dazzerman. The solution is correct and working for sample data but I am getting error "The value did not change after the Loop Expression", Although, I am getting an output but it is not correct as some dates are missing. I tried a lot but couldn't identify the reason. May be my sample data doesn't adhere to whole data. Actually, I just want to add the Qty for last 21 days for same item. Like this:

item	Qty	Date	Sum_21_Days
A	3	2019-10-11	9
A	5	2019-10-08	6
A	1	2019-10-05	3
A	2	2019-09-15	4
A	1	2019-09-09	2
A	1	2019-09-01	1
B	3	2019-10-11	8
B	2	2019-10-08	5
B	3	2019-10-05	4
B	1	2019-09-15	5
B	4	2019-09-09	4
C	1	2019-10-11	4
C	2	2019-10-08	3
C	1	2019-10-05	2
C	1	2019-09-15	6
C	0	2019-09-09	5
C	4	2019-09-01	5
C	1	2019-08-28	1

Sorry for the inconvenience. It would be very helpful of you if you can help me with this.

Thanks.

[PKN]

Hey, I have solved the error, it was because of null values. Thanks a lot to both of you. Really appreciate your quick response.

[Dazzerman]

Nice one!

I had time to run your updated data through my copy of the workflow and didn't get an error, then had to go to a meeting so couldn't investigate further. Glad you found the problem and have your solution.

[PKN]

No worries. You already helped me enough 🙂 Your solution was applicable for new sample data set also but my whole data set was different from sample data set in terms of date null values, that's why I was getting the error. But, all solved now. Thanks again.