2024: Week 11 - Solution
Solution by Tom Prowse and you can download the workflow here.
The challenge this weeks focusses on manipulating dates so that we add in an additional month to the year so we have 13 months in total.
Step 1 - Every day of year
First off we want to create a row for every day of the year. We are given our start and end date within the input, so first we want to remove any Null values then create our date field so that it includes the year:
Date
[Date] + "2024"
Then we can change the data type to a date so we have a nicely formatted date field.
At this stage we should have two rows with two dates - 01/01/2024 and 31/12/2024.
We can then use these dates to create a row for each day between each of these using a New Rows step.
Now we have every day of the year we can then create a field for the Month number and Day of the Year number.
To do this we will duplicate the Date field, then convert this to Month Number using the in-built functionality
However, we can't do this with the Day of the Year so we need to use a calculated field to find this:
Day of Year
DATEPART('dayofyear',[Date])
At this stage our table should look like this:
Step 2 - New Dates
Now we have all of the days of the year we can start to create the new dates that fit into the 13 months scenario instead of 12.
We are working towards the assumption that the first 28 days are month 1, the next 28 days are month 2 etc so we can calculate the new month using a calculated field:
New Month
CEILING([Day of Year]/28)
We can then use this New Month field to create a new 'day of the month' by using a Rank calculation where we group by New Month, then rank based on the Date field:
At this stage we have the New Month and New Day of Month field
Finally we can use these to calculate the New Date field:
New Date
IIF(LEN(STR([New Day of Month]))=1,'0'+STR([New Day of Month]),STR([New Day of Month]))
+
'/'
+
IIF(LEN(STR([New Month]))=1,'0'+STR([New Month]),STR([New Month]))
+
'/'
+
STR(2024)
This can look a little confusing at first, but each section represents a Day, Month, or Year. The first & second part is the Day & Month sections, where we are adding a leading 0 to each number if it's a single digit (the LEN function counts the characters). Then finally adding the year 2024 at the end.
After removing the 'old' date fields our table should look like this:
You'll note that there are a couple of extra days that go over into month '14' so ideally these will go to the next year, but for now we can leave them as they are.
Step 3 - Prepare Output
The final step for this challenge is to only retain the dates where the month has changed in the new system.
For this we can use a filter to match up the 'old' Month and the New Month fields
[Month]!=[New Month]
We then have our list of dates that have changed.
The last task is to make sure these are ordered in date order which we can use a Rank calculation:
After this we can hide this field so that it isn't shown in the output but the table is sorted as we would expect.
Our output should look like this:
After you finish the challenge make sure to fill in the participation tracker, then share your solution on Twitter using #PreppinData and tagging @Datajedininja, @JennyMartinDS14 & @TomProwse1
You can also post your solution on the Tableau Forum where we have a Preppin' Data community page. Post your solutions and ask questions if you need any help!
