sum upto last n dates data

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.

Developer Tools

Accepted answers

OllieClarke

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

21.yxmd

Dazzerman

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_query.yxmd

All comments

OllieClarke

Hope that helps,

Ollie

21.yxmd

Dazzerman

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_query.yxmd

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.

Quick Links

This months top contributors

LanisC 335

hhaigh1 280

erich_shiino 280

FláviaB 262

JORGE4900 235