2023: Week 48 - Solution
Solution by Tom Prowse and you can download the workflow here.
Step 1 - Selected Reporting Year
First step this week is to input the data and then create a parameter that will allow the user to select a reporting year.
The parameter should be a whole number with the options of 2020, 2021, 2022, 2023, & 2024 to choose from.
We can then use this in our workflow as a calculated field containing just this parameter:
From here we can utilise this field to create a start and end date for the selected period.
For the start date we use the following calculated fields:
Start
DATE(DATETRUNC('week',MAKEDATE([Year]-1,2,1),'Monday'))
This will find the Monday before the 1st Feb.
End
MAKEDATE([Year],12,31)
The end date is always the end of the selected year.
Step 2 - Reporting Year
Next we want to fill out the dates for the reporting year by first calculating the start of the reporting year:
Start of Reporting Year
DATE(DATETRUNC('week',MAKEDATE([Year],2,1),'Monday'))
Then we can use the 'New Rows' step to fill in the dates between our Start and End dates.
Now we have the fully filled in table we can create a reporting year field based on the logic in the requirements.
Reporting Year
IF [Calendar Date]<[Start of Reporting Year]
THEN [Year]-1
ELSE [Year]
END
At this stage we can remove all of the fields so only Calendar Date and Reporting Year are remaining:
Step 3 - Reporting Day, Week, Month
Next we can calculate the other date parts based on the calendar dates.
Reporting Day
This is a rank calculation grouped by Reporting Year:
Reporting Week
First we need a to flag when a new week starts using this calculated field:
New Week Flag
IF [Reporting Day]%7=1
THEN 1
ELSE 0
END
Note, the % is the modulo operator - more info here.
Then we can use this to calculate the reporting week by using a running calculation. In the running calculation we group by reporting year, order by calendar date (asc), then use the New Week Flag to compute the running sum:
Reporting Month
For this we can utilise the CEILING function to round the reporting weeks when they have been divided by 4.
CEILING([Reporting Week]/4)
Then finally we can limit the table so that it is only showing the dates from the parameter that we selected at the start of the challenge. We can use the parameter in a filter calculation where YEAR([Calendar Date])=[Parameters.Calendar Year]
The final output should look like this (when 2023 is selected):
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!
