Week over Week (or MoM) Automated Results

Romano el Polako

Hello Smart Heads!

I hope you are doing great!

This time I have a question about semi-automated WoW (week-over-week) calculation for specific performance metrics.

Thats how it looks as for now:

In WoW for SLA I have:

=IFERROR(([W03]@row - [W02]@row) * 10000, "")

below I have also formula for % change:

=IFERROR(([W03]@row - [W02]@row) / [W02]@row, "")

Considering that year has 52/53 weeks, every week person maintaining the report (all done automatically via data shuttle), would need to move the scope of formula, see:

I was thinking about using parent row to change weeks in scope:

These numerals to be a trigger for changes in formulas below.

I was trying to use INDEX(MATCH but without success, another option (pretty savage) would be 52x IF formulas:

=IF(Target3 = "2", [W02]@row) etc.

Obviously it is possible, but I would like to ask you about any other simplet solution that could solve my issue.

Please feel free to reach out in case of any questions,

Thanks,

Roman

Paul Newcome

Are they blank until you get to those weeks, or are the weeks pre-filled? Can we use the logic that we want to pull the last filled in cell and then subtract from it the second to last filled in cell? Will there ever be blank weeks in the middle?

Romano el Polako

Hey @Paul Newcome

Thank you for your response!

Weekly results will be updated automatically, on the weekly basis (every Monday) once we receive raw data from our stakeholder. I am currently using Data Shuttle, hence once the file is attached to raw data dump, results will appear in respective column.

The problem is that sometimes weekly file can include results corresponding to previous week (processing took longer than a week).

Example, in W1 file I still had results for W52 and W53.

Nevertheless, solutions you are proposing should work in vast majority of cases. Can something like this be set up for all (53) columns?

Thanks,

Roman

Paul Newcome

The easiest way to do this would be to use an INDEX function to pull from the row and a WEEKNUMBER function in reference to today's week number.

=INDEX([W01]@row:[W53]@row, WEEKNUMBER(TODAY())) - INDEX([W01]@row:[W53]@row, WEEKNUMBER(TODAY()) - 1)

Romano el Polako

Thank you @Paul Newcome

I will give it a try and let you know!

Romano

Romano el Polako

Hey @Paul Newcome

I have tried to use the formula, but getting Invalid Value Error, see:

Any idea what went wrong here?

Thanks,

Roman

Paul Newcome

Which cell are you putting the formula in? I see three cells in your screenshot containing that error.

Romano el Polako

Hey Paul!

Sorry for any confusion caused!

Let me explain the request in more details.

First round:

In respective row for column WoW (week over week) have the formula to provide:

1) Value for Current Week (#5)

2) Value for Previous Week (#4)

Second Round:

Once I have these two formulas, I can proceed with caluculation:

(Wcurrent - Wprevious) * 10000 or any other calculation of weekly trends.

Third Round:

Weekly change will later be managed by conditional formatting, positive = green, negative = orange.

*Alternative

Have two cells with week numbers (current and previous) and use these cells and values for index(match.

Final calculation of two desired formulas, still to be presented in [WoW] column for respective row, like in example [WoW]7.

---------

The long story short, I would like to have something scalable as I have a lot of similar parent KPIs groups with respective MPs.

Please let me know if it makes sense, thanks!

Romano

Paul Newcome

What cell are you putting the formula I provided in?

Romano el Polako

Hey,

from

=WoW4

=WoW6

=WoW7

and next

Thanks

Paul Newcome

What happens if you put this in that cell:

=COUNTIFS([W01]@row:[W53]@row, OR(@cell = "", @cell <> ""))

Romano el Polako

Hey Paul,

Once I put it there I get 53, count of columns:

Thanks,

Roman

Paul Newcome

And what do you get with these two?

WEEKNUMBER(TODAY())

WEEKNUMBER(TODAY()) - 1

Romano el Polako

Hey,

6

5

so actually, we will be comparing, following:

WEEKNUMBER(TODAY()) - 1

WEEKNUMBER(TODAY()) - 2

as the results are always for previous week (-1), to be compared with -2 week

Thanks,

Roman

Paul Newcome

What does this one do now?

=INDEX([W01]@row:[W53]@row, WEEKNUMBER(TODAY()) - 1)

Romano el Polako

Hey,

Sadly still the same error, #INVALID VALUE

I have no idea how to get this info to the cell, maybe It would be better if I share a file as public?

Thanks,

Roman