2023: Week 15 - Solution
Solution by Tom Prowse and you can download the workflow here.
Step 1 - Dates
After inputting the data we first want to focus on the dates on the x-axis.
Within the table we can filter on F2 and keep only null values, then in F3 we can exclude all the null values. This will help us to have the month and day combinations so we can then use a rank calc to create a row id field.
At this stage our table should be wide with a separate field for each day:
From here we can then pivot the data by using a wildcard pivot of all the 'F' fields:
Then pivot it back the other way using the Row field and the Max of F:
Then rename the Pivot Names to Colum, 1 to Day, and 2 to Month.
Within the Month field we want to remove any spaces so that we have a string with each of the month names.
Then we want to fill in the null values with the correct month.
First we can duplicate the column field, and then remove all letters from the field. This leaves us with a whole number and we can rename it to order.
From here we can create a separate step, and exclude any null values from the month field. This leaves us with the two months and the order in which they appear in the table.
Using the Order field we can join back to the previous step where Order >= Order
Then we need to calculate the Max for each order using a Fixed LOD:
We can then limit the table so that the max order equals the order - [Calculation1]=[Order-1]
All we need to do now is tidy the table and create the correct date field.
The table should now look like this:
Step 2 - Years
Next we want to focus on the Years. We need to create a new branch right back at the start of the workflow. Here we want to exclude null from F2 then make sure F13 and F3 are whole numbers.
We can then Pivot the F values using the wildcard pivot and make sure all nulls have been excluded:
After renaming the fields the table should look like this:
To create the full date we can now join this with our other date workflow using the Column field.
Step 3 - Filter Dates
Finally we need to filter the year field so we keep all values <=2023.
Then we can create the date field for the Easter Sunday by combining the date and year:
If we then want to order the dates we can create a rank calculation and hide this field from the view:
The final list of dates should look like this:
