Calculating Net Change over Multiple Fields

Question

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 RepWeek_01Week_02...Week_50George Washington

1881880250John Adams4500360100...............

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 Answer

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