5 Replies Latest reply on Mar 4, 2018 7:30 PM by Zhouyi Zhang

# YOY comparison with shifted weeks

Hi guys,

I am trying to create a YOY comparison with shifted weeks. That means that the every week of the current year will be compared with the next week of the previous year. To give an example:

• Week 1 2018 will be compared with Week 2 2017
• Week 2 2018 will be compared with Week 3 2017
• Week 3 2018 will be compared with Week 4 2017

I managed to create a YOY calculation but that compares only equal weeks. For example Week 1 2018 with Week 1 2017. Do you have any ideas how we can create the shifted comparison?

Workbook attached. Thank you!

• ###### 1. Re: YOY comparison with shifted weeks

Hi, Mike

Please find my solution attached as well as below screenshot.

Hope this helps

ZZ

• ###### 2. Re: YOY comparison with shifted weeks

Hi Zhouyi,

Unfortunately the YOY comparison is still based on the same week of last year. In addition the Week 5 of the current year is not showing with this solution.

Is there any other idea?

• ###### 3. Re: YOY comparison with shifted weeks

Hi Mike,

Try changing calculation 1 to

IF [Fiscal Year] = 'FY17' THEN

[Fiscal Week]-1

ELSE

[Fiscal Week]

END

then edit the week lookup filter and add week 6.

Regards,

Ivan

• ###### 4. Re: YOY comparison with shifted weeks

Hi Ivan

I have tried your solution but the problem is that now the sales are not attributed in the correct week for the FY17. For example now for FY17 we have:

• Week 1 with 10435 sales     -> This should be 8731
• Week 2 with 3457 sales       -> This should be 10435
• etc.

It is important to show the correct amount of sales per week but then calculate the YOY based on the next week of the previous year. Do you maybe have another suggestion on that?

Thanks!!

• ###### 5. Re: YOY comparison with shifted weeks

Hi, Mike

Please find my updated solution attached as well as screenshot below

ZZ

1 of 1 people found this helpful