2023: Week 29 - Solution
Solution by Tom Prowse and you can download the workflow here.
This week we take our first look at some new features within Tableau Prep! These include the Lookup Function, Moving Averages, and adding a TimeStamp to a file name when we output the data.
Step 1 - Filter Dates
First we want to input our data table, then we need to ensure that the date field is in the correct format. We only want to look at dates on a monthly basis, therefore we can use the DateTrunc function to ensure that all dates are rolled back to the 1st of the month.
Date
DATE(
DATETRUNC('month',[Date]))
We can then use a filter on this field to remove any dates after July
Now our table should look like this:
Step 2 - Monthly Sales
We can now aggregate the table so that we have our sales at a monthly level. Within the aggregation we group by Date, Store, and Bike Type, then Sum Sales & Profit.
Now we have the data at a monthly level, we can now fill in any months that might be missing.
First we need to use the lookup function to find the next sales month for each store & bike type:
Next Month
{ PARTITION [Store], [Bike Type] : { ORDERBY [Date] asc : LOOKUP([Date],1)}}
This will identify the next month in the data set, then we can subtract a month from this to get the 'gap' between months:
Next Month
DATE(DATEADD('month',-1,[Next Month]))
Then finally we can fill in the missing Null values by identifying the latest date using an LOD
Latest Date
Then we can replace the nulls with the Latest Date
Next Month
IFNULL([Next Month],[Latest Date])
Now we have the missing months, we can fill the table out by using the New Rows step where we want to add rows between Date <= Next Month in 1 month increments where we copy from the previous row.
At this stage our table should look like this:
The last step here is to ensure any months that have been filled should have a 0 values in Sales & Profit.
Sales
IF [Month]=[Date]
THEN [Sales]
ELSE 0
END
Profit
IF [Month]=[Date]
THEN [Profit]
ELSE 0
END
Step 3 - Moving Average & TimeStamp
The final step in this weeks challenge is to calculate a 3 month moving average of profit. We can find this new calculation in the visual analytical calculation section in the Create Calculated field menu:
We can then set it up using the visual interface where we group by Store, Bike Type, and order by Month in an ascending order. Then compute using Profit and set the results to Avg and previous 2 values
The last task we need to do this week is to add the time stamp onto the output file name. Within the Output step you can now add system parameters to the file name which include Run Date & Run Time.
In this case we want to add the run date month name & year number:
You can download the output from here.
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!
