Monthly Savings Workflow

[Fiona99]

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 

[atcodedog05]

Hi @Fiona99 

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

Workflow:

Hope this helps : )

[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!

[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.

So the aim is to see how much savings we made by month on a certain part on a Month by Month basis.

I hope that makes sense.

Thanks 

[atcodedog05]

Hi @Fiona99 

Can you provide a formula-driven excel so that we get an understanding.

[Christina_H · Accepted solution]

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

[Fiona99]

Thanks very much! 

I'll try apply it to my data set and let you know how I got on.

Thanks again 

[atcodedog05]

Great job on cracking it @Christina_H ‌‌‌‌ 🙂👍 This one was bit of a confusing one for me 😅