Hi all, I'm struggling to create a workflow to show Monthly revenue savings based on the previous months data by Item Number. I've Parsed out the dates in the Transpose Tool but can only do this for one Value at a time. I have to show the Monthly savings per Month based on the previous Months cost Increase or Decrease. Sample Data Below if anyone can help. invoice datevendor nameitem numberqtyamount 28/06/2021Fiona 416666202210.819/05/2021Fiona 416666303316.201/04/2021Fiona 416666151658.0928/06/2021Fiona q45yq5y101105.419/05/2021Fiona q45yq5y51901.628/06/2021Fiona 789999104884.219/05/2021Fiona 789999209738.428/06/2021Fiona q45yq5y50688.519/05/2021Fiona q45yq5y1945979.6228/06/2021Fiona 7899992048399.619/05/2021Fiona 35351536299.720/07/2021Fiona 5.37E+091638719.6818/06/2021Fiona q45yq5y3072599.4 I'd like to get a result like below with possible % Value also. PartJan Savings Feb SavingsMarch Savings 41666610666678890000 Thanks

Monthly Savings Workflow

Hi all,

I'm struggling to create a workflow to show Monthly revenue savings based on the previous months data by Item Number. I've Parsed out the dates in the Transpose Tool but can only do this for one Value at a time. I have to show the Monthly savings per Month based on the previous Months cost Increase or Decrease. Sample Data Below if anyone can help.

invoice date	vendor name	item number	qty	amount
28/06/2021	Fiona	416666	20	2210.8
19/05/2021	Fiona	416666	30	3316.2
01/04/2021	Fiona	416666	15	1658.09
28/06/2021	Fiona	q45yq5y	10	1105.4
19/05/2021	Fiona	q45yq5y	5	1901.6
28/06/2021	Fiona	789999	10	4884.2
19/05/2021	Fiona	789999	20	9738.4
28/06/2021	Fiona	q45yq5y	50	688.5
19/05/2021	Fiona	q45yq5y	19	45979.62
28/06/2021	Fiona	789999	20	48399.6
19/05/2021	Fiona	3535	15	36299.7
20/07/2021	Fiona	5.37E+09	16	38719.68
18/06/2021	Fiona	q45yq5y	30	72599.4

I'd like to get a result like below with possible % Value also.

Part	Jan Savings	Feb Savings	March Savings
416666	10666	6788	90000

Thanks

Data Investigation

Accepted answers

Christina_H

I think I see, here's an updated version. I've borrowed the dynamic rename from @atcodedog05 to tidy up the column names!

Monthly savings.yxmd

All comments

atcodedog05

Hi @Fiona99

Here is how you can do with help of crosstab tool and dynamic rename tool.

Workflow:

Hope this helps : )

Savings.yxmd

Christina_H

I've taken a very similar approach but with you saying the savings were based on the previous month I've used a multi-row formula to calculate the differences.

Hopefully between us we've covered what you need!

Monthly savings.yxmd

Fiona99

Hi, Thanks for that. Sorry I don't think I explained it correctly:

I'd have to Divide the Amount paid by Qty per Month to calculate the Price of the Item per Month, then Multiple the price Difference by the Current Month QTY to figure out the savings or non savings we made on that part month by month.