13 Replies Latest reply on Jul 16, 2018 11:55 PM by Mavis Liu

# Last five weeks of the current period for 2018 and values for the same period in 2017

Hi All,

1) Create a report that will display values for the last five weeks of the current period for 2018 and values for the same period in 2017

2) Using the values from above, display the YoY.  Divide the value for a specified 2018 week with the value for the same week in 2017 minus 1

e.g

(week 26 of the current year/week 26 of the previous year) -1

(week 25 of the current year/week 25 of the previous year) -1

(week 24 of the current year/week 24 of the previous year) -1

Thanks

Regards

Tony

• ###### 1. Re: Last five weeks of the current period for 2018 and values for the same period in 2017

Hi Tony

Could you please attach sample data as twbx format.

Thanks,

Shin

• ###### 2. Re: Last five weeks of the current period for 2018 and values for the same period in 2017

Hi All,

I have attached a copy of the workbook (Twbx) and excel spreadsheet to this message.

The first worksheet shows the report in the format I want it.  I have hard coded the year 2017 and 2018 into the report, but I want a formula to do this.

In worksheet 2, I want to calculate the difference between a specified week in the current year and the corresponding period in the previous year.

In the workbook some of the worksheets have no information on them.  This is because I am not sure how to achieve the information I require.

Please see attachment for illustrations of what I am trying to achieve with each of the worksheets.

Thanks

Regards

Tony

• ###### 3. Re: Last five weeks of the current period for 2018 and values for the same period in 2017

Hi Tony,

You could use table calculations and do a percent difference based on specific dimensions year of order date:

Then create an ad hoc calculation by double clicking in the columns shelf and typing in last() = 0. Make sure the calculation is being calculated by year of order date again:

then move this into the filters shelf and set it to true.

Thanks,

Mavis

• ###### 4. Re: Last five weeks of the current period for 2018 and values for the same period in 2017

Hi Mavis,

Thank you for replying to my query. For YoY % Actual variance tab this is exactly what I am looking for.

1 Actual (2017) vs Budget (2018)

2 YoY % Actual (2017) vs Budget (2018)

3 Difference (YoY % Actual vs Budget - YoY % Actual variance

I have already attached an excel sheet to this query illustrating what l am a trying to achieve.

Thanks

Regards

Tony

• ###### 5. Re: Last five weeks of the current period for 2018 and values for the same period in 2017

Hi Tony,

Please see the attached workbook with details below:

1)

matches with yours:

2)

Matches with yours:

For 3, I get:

and you get:

Please see the attached workbook. If you'd like me to go through anything, please let me know.

Thanks,

Mavis

• ###### 6. Re: Last five weeks of the current period for 2018 and values for the same period in 2017

Hi Mavis,

Thank you, this is fantastic.  I really appreciate all the hard work you've put into this, even at such short notice.  Thanks again

Regards

Tony

• ###### 7. Re: Last five weeks of the current period for 2018 and values for the same period in 2017

Hi Tony,

No problem at all!

You can do this by opening up the thread, unfortunately you cannot mark it as answered within your inbox.

Thanks,

Mavis

• ###### 8. Re: Last five weeks of the current period for 2018 and values for the same period in 2017

Hi Mavis,

Can you please explain to me how you calculated the Actual (2017) vs Budget (2018), I am getting a different result when I tried to do the same thing.  Please see screenshot of what I am getting. Thank you

Regards

Godwin

• ###### 9. Re: Last five weeks of the current period for 2018 and values for the same period in 2017

Hi Tony,

I use this calculation and put it into the text. So when it's 2017 it uses sales, then 2018 uses the budget amount:

Thanks,

Mavis

• ###### 10. Re: Last five weeks of the current period for 2018 and values for the same period in 2017

Hi Mavis,

I used the same formula you used, but I got a different result. Please see attachment.

Thanks

Regards

Tony

• ###### 11. Re: Last five weeks of the current period for 2018 and values for the same period in 2017

Hi Tony,

This is because you need to activate your blend based on week. You see how your budget for Furniture is the same across all the weeks? This is because Tableau does not know that your linking up your datasets based on week, so therefore it gives the total sum for the category rather than breaking it down by weeks.

To activate the week as a blend, click on your secondary data source and click on the chain so that it becomes red:

Then you should get the below:

Thanks,

Mavis

• ###### 12. Re: Last five weeks of the current period for 2018 and values for the same period in 2017

Hi Mavis,

Thank you so much for helping me out on this!

Regards

Tony

1 of 1 people found this helpful
• ###### 13. Re: Last five weeks of the current period for 2018 and values for the same period in 2017

Hi Tony,