Method 2: Converting a dimension to a Date Format
The next challenge is displaying only the last week’s data without the period dimension in the view. There are a couple ways to do this, but the most versatile is converting the period into a date format.
Since we have the year, period and week in the Period dimension, we can write a few calculations to give us the week number of the year and then approximate a date. It doesn’t have to be exact for our purposes.
You could nest all your calculations into one, but I prefer to simplify and use separate fields. First, we need to grab the year from each period. This one is pretty straightforward, and we can use the LEFT function to grab what we need since the year is at the beginning of each string.
Next, we need to extract the week number. This one’s a little more complicated since it is dictated by the period and by the week. Since the strings are two lengths (when the periods get to 10 and up), we can write a simple IF statement to evaluate the length, retrieve the period and week number, and then perform some math to calculate the correct week number of the year.
This calculation determines the string length, retrieves the period value, multiplies it by four, then adds it to the week value, while also subtracting four from the final value. We can see this in action by putting the fields in a table to make sure it’s working.
Now that we have the week, we could use a MAX function to display the latest week, but would have to do some more work to get that to work across years. Another option is to take it one more step and convert the year and week into a date so we can use the “Previous Week” function to always show the latest period.
To convert to a date, we can combine the DATEADD and DATE functions.
This calculation creates a start date of 1/1 for each year in the data set and then adds the appropriate week number to it (minus 1). We now have a date that corresponds with each period in our data set! The day of the week that 1/1 falls on doesn’t matter in this scenario. All we care about is assigning a period and week to the corresponding week of the year.
From here we can use built-in Tableau date filter functions to show the data we want. Click this link to download the workbook tutorial for reference. I encourage you to download, check out the calculated Date fields, and repurpose to solve your own challenges related to non-date time dimensions. Remember that if there’s a will, there’s usually a way to do it in Tableau. Check out our other data viz blog posts for more tips, tricks and hacks to coax Tableau into doing what you need it to do.