12 Replies Latest reply on Jun 19, 2018 1:32 PM by Tony Smith

# How to displace and calculate the last five weeks of the current year and the last five weeks of the previous year Sales

Hi All,

Can anyone please tell me how to report and calculate the last five weeks of the current year and the last five weeks of the previous year Sales?  Please see additional information below.  I have also attached a spreadsheet to illustrate what I'm trying to achieve.

Also, the last five weeks of the current year vs the last five weeks of the previous year variance for the last 3 years.

- Weekday is the same when comparing current vs Previous years

- Day fall on the same for year on year comparison 52 weeks

e,g 13/05/2018

14/05/2017

15/05/2016

Thanks

Regards

Tony

• ###### 1. Re: How to displace and calculate the last five weeks of the current year and the last five weeks of the previous year Sales

Hi Tony,

So it looks like you're comparing it against the ISO8601 weeks as 07/05/2017 and 06/05/2018 are both week 18.

So Tableau doesn't use ISO, so we'd first have to convert it, I have attached a workbook below and this is the calculation I made to create the ISO week off the actual dates:

iso:

INT((Datepart('dayofyear', (dateadd('day',INT(datediff('day', #1900-1-01#, [Date])/7)*7  + 3,#1900-1-01#))) + 6)/7)

I then created a calculated field to find the previous 5 weeks in the current year, then the same period for the previous year:

5 weeks:

if   (DATEPART('week',TODAY())) <

[iso]+6

and DATEPART('year',TODAY())= DATEPART('year',[Date])

and

(DATEPART('week',TODAY())) >

[iso]

then 'True'

elseif

(DATEPART('week',TODAY())) <

[iso]+6

and

(DATEPART('week',TODAY())) >

[iso]

then 'True'

else 'False'

END

Then I placed the year and iso onto the columns shelf, and values in rows. Then placed 5 weeks into the filters shelf and set it to true. Ultimately I get this:

To get the difference, please review the second tab in the workbook.

Right click on Sum(value) in the rows shelf and select quick table calculation -> difference:

Right click again and select edit table calculation so that we can define the scope and direction:

Select specific dimensions and tick on year of date and leave iso unticked. This means for every ISO, calculation the difference by every year.

Now on the bottom right hand side you may see a null indicator. This is because in my case, 2017 has no prior year to compare to. Please right click and hide the indicator, or click on it to set it to default position.

Thanks,

Mavis

• ###### 2. Re: How to displace and calculate the last five weeks of the current year and the last five weeks of the previous year Sales

Hej Tony,

You will need to prepare your data for this kind of analysis. Here is an example of how you could do that with Tableau Prep, and then move to Tableau:

1. I selected cleaned with Data Interpreter, which will generate multiple sheets that I can pivot and union together.

Then I generated Tableau extract (all attached below). Then from there, I believe you can finish the job with simple calculation e.g.

Current Year Sales

```IIF(YEAR({MAX([Date])}) = YEAR(Date), Values, NULL)
```

Last Year  Sales

```IIF(YEAR({MAX([Date])}) - YEAR(Date) = 1, Values, NULL)
```

• ###### 3. Re: How to displace and calculate the last five weeks of the current year and the last five weeks of the previous year Sales

Hi Prayson,

Thank you for your response to my question. I will apply the solution that has been recognised and will keep you updated.

Thanks again for the quick response.

Regards

Tony

• ###### 4. Re: How to displace and calculate the last five weeks of the current year and the last five weeks of the previous year Sales

Hi Mavis,

Thank you for your response to my question. I will apply the solution that has been recognised and will keep you updated.

Thanks again for the quick response.

Regards

Tony

• ###### 5. Re: How to displace and calculate the last five weeks of the current year and the last five weeks of the previous year Sales

Hi Mavis,

I am new to the tableau community. I can't find the workbook you attached to your reply. Can you please resend it or tell me how to find it?

Thanks

Regards

Tony

• ###### 6. Re: How to displace and calculate the last five weeks of the current year and the last five weeks of the previous year Sales

Hi Mavis,

Sorry to be asking too many questions. I have applied your recommendation to the sample - superstore Data Source.

I tried to attach a copy of the workbook to this message but I can't find the attachment button.

I got the solution I was looking for in sheet 1 and sheet 2.

I only got outputs for 2018 and 2017 in sheet 1.  Please see screen shot below.

And in sheet 2, I only get variance for 2018. Can you please help to amend your solution to give me 2017 and 2016 variances as well?

Thank you for all your help.

Regards

Tony

• ###### 7. Re: How to displace and calculate the last five weeks of the current year and the last five weeks of the previous year Sales

Hi Mavis,

I am now getting data for 2016, 2017 and 2018.  I copied your 5 weeks code and amended it.  See below for the update I made to your code.  I am not sure if this is the right thing to do, but I am getting data for all the years I want.

if   (DATEPART('week',TODAY())) <

[iso]+6

and DATEPART('year',TODAY())= DATEPART('year',[Order Date])

and

(DATEPART('week',TODAY())) >

[iso]

then 'True'

elseif

(DATEPART('week',TODAY())) <

[iso]+6

and

(DATEPART('week',TODAY())) >

[iso]

then 'True'

elseif

(DATEPART('week',TODAY())) <

[iso]+6

and

(DATEPART('week',TODAY())) >

[iso]

then 'True'

elseif

(DATEPART('week',TODAY())) <

[iso]+6

and

(DATEPART('week',TODAY())) >

[iso]

then 'True'

else 'False'

END

The sales amount from Monday to Saturday are all displaying the right data, but all the Sunday data are displaying the sales amount for the following Sunday instead of the Sunday before. E.g. the current week 24 is displaying \$3,897 for 17/06/2018 instead of \$2,513 for 10/06/2018. Please see screenshot below.  Can you please help me with this?

I'm not sure why the attachment button is not showing for me. I want to attach the workbook to this message but I can't do so.

Thank you

Regards

Tony

• ###### 8. Re: How to displace and calculate the last five weeks of the current year and the last five weeks of the previous year Sales

Here is the workbook.

Regards

Tony

• ###### 9. Re: How to displace and calculate the last five weeks of the current year and the last five weeks of the previous year Sales

Hi Tony,

The reason why you couldn't see my previous attachment may be because you were reading this thread within your messages. To see attachments, please go into the actual thread itself.

With regards to your question, the 17th June belongs to week 24, so that's why that sunday is still coming up under that date:

If you want to shift it by one day, you'll have to update the ISO to this:

INT((Datepart('dayofyear', (dateadd('day',(INT(datediff('day', #1900-1-01#, [Order Date])/7)+1)*7  + 3,#1900-1-01#))) + 6)/7)

Thanks,

Mavis

• ###### 10. Re: How to displace and calculate the last five weeks of the current year and the last five weeks of the previous year Sales

Hi Mavis,

Thank you for your response.  I now understand why the report shows the figures for the current week Sunday.  Is there anything I can do to get the report to show the previous Sunday instead of the current week's Sunday?  This is what has been requested from me.

This is all the values that I am expecting to see in the report below for week 24.

All of these values are showing in the report below apart from \$2,513, instead is showing \$3,897.

I have tried the query below for iso, all the values moved back by 1 week.

INT((Datepart('dayofyear', (dateadd('day',(INT(datediff('day', #1900-1-01#, [Order Date])/7)+1)*7  + 3,#1900-1-01#))) + 6)/7)

I have attached a copy of the workbook to this message.

Thank you.

Regards

Tony

• ###### 11. Re: How to displace and calculate the last five weeks of the current year and the last five weeks of the previous year Sales

Hi Tony,

In that case please use weeks in tableau and bring it to the columns shelf and update the 5 weeks filter to the below:

Thanks,

Mavis

• ###### 12. Re: How to displace and calculate the last five weeks of the current year and the last five weeks of the previous year Sales

Hi Mavis,

Thank you for all the help you've given me over the past five days I really appreciate it.

Regards

Tony