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
Solved! Go to Solution.
Hi @Fiona99
Here is how you can do with help of crosstab tool and dynamic rename tool.
Workflow:
Hope this helps : )
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
I think I see, here's an updated version. I've borrowed the dynamic rename from @atcodedog05 to tidy up the column names!
Thanks very much!
I'll try apply it to my data set and let you know how I got on.
Thanks again
Great job on cracking it @Christina_H 🙂👍 This one was bit of a confusing one for me 😅