Calculating Net Change over Multiple Fields

[alexg1]

Hello,

I've been trying to construct a flow that will calculate net change in sales week to week on a Year-to-date basis. My data looks something like this:

Sales Rep	Week_01	Week_02	...	Week_50
George Washington	188	188	0	250
John Adams	450	0	360	100
...	...	...	...	...

What I want to do is calculate the difference between week 1 and week 2, then calculate the difference between that number and Week 3, and so on until I reach the current week. I could probably throw something together in the formula tool, but I'm trying to find a less cumbersome solution to arrive at my answer. Any assistance would be greatly appreciated!

[AngelosPachis · Accepted solution]

Hi @alexg1 ,

I believe you can do that with a multi-row formula tool (if I understand the requirements correctly)

If the week number is less than or equal to 2, then your formula is set to estimate the difference between those 2 weeks. If the week number is greater than 2, then your formula will estimate the difference between the prior diff value and that of the current week.

So for week 3, 47-35=12 (diff of weeks 2 and 1 - week 3).

If you have more than one sale reps, you can group by the sale rep and that calculation will restart for each sale rep in your dataset.

Hope that helps,

Regards,

Angelos

[alexg1]

This put me on the right track! I had to make a few tweaks but this did help, thank you!