An Easter themed #PreppinData for 2024 week 13. Preparing sales of products in the 12 weeks running up to Easter to allow for easy comparison of the period across years in Tableau Desktop.

A nice one step solution this week (see screenshot at the end of this post): a FIXED level of detail calc to get the first sale date per year; then date calcs to get the week, day and day order.

This week marks a quarter of a year learning Tableau Prep. I started with the Tableau getting started guide (2 hours), committed around an hour a week for 13 weekly #PreppinData challenges (a great resource and progression of learning), answered a handful of Tableau Prep questions on the Tableau community forums (5 hours), and blogged about my learnings to reinforce them (5 hours). 25 hours all up and I feel like I’ve got a good grasp of the product and it’s capabilities. It’s a great tool for analysts and those needing to do ad-hoc but often repeatable data preparation, cleaning and transformation prior to analysis. Even better, it’s included with your Tableau Creator license if you’re using Desktop or Cloud as a creator! Definitely give it a go if you use Tableau and have a need to tidy data.

#PreppinData 2024 week 12, graduate student loan repayment calculator. Good to try out the “value ranges from two fields” option within a “new rows” step. Like some others my interest figure is a little different from the supplied output, however the calc appears to be the same. I also shortcutted the join onto repayment info for undergraduates with a filter (down to just the undergrad row), and joiner fields allowing a simple join on 1=1.

Week 11 of #PreppinData, and the question: what if there were 13 months in a year? Nice concept to have consistent 28 day months, with 4 weeks per month and each month starting on a Monday and ending on a Sunday. As we found out when we expanded the two row data set though … it’s not as neat as it seems, ending up with a spare day (or two in a leap year).

Part one of my flow answers Jenny’s question “which dates would change months?”. Output gives me 190 as expected.

Part two of the flow looks at Carl’s question of “what the new month(s) would be called”. Turns out the extra month most logically slots in between June and July. This is based on which old month is most associated with a new month number (the number of days of each new month that fall in an old month). And then the “average” of June and July (based on ASCII codes) is Juno. This may (or may not) be Dominican slang for extremely drunk, which coincidentally may (or may not) be what you’d need to be to suggest changing the current calendar in the first place! My preference for the extra day or two at the end is Extrember … as it’s a little extra after December.

My solution for the #PreppinData 2024 week 10 challenge follows below. Cool to check out the New Rows step this week to fill in the missing days in the data set.

Finishing #PreppinData intermediate-level month with a 1-step solution … fun, but definitely harder and less easy to maintain!

#PreppinData 2024 week 8 – a “what if?” analysis of two different customer loyalty reward systems for Prep Air. Aiming to identify cost and number of customers benefiting.

The “estimated yearly flights” calculation tripped me up for a while, out thinking it with a datediff on days, and only when the flights spanned more than a year. The challenge just required a division by the number of years flown over! I enjoyed expanding the data set throughout the flow (pivoting the benefits, joining onto cost per benefit, and then joining onto those tiers less then or equal to each customer’s tier) to then roll back up at the end.

#PreppinData 2024 wek 7:

The #PreppinData 2024 week 6 challenge was to find the latest salary per staff member and summarise their tax position given UK income tax bands.

We’re now into intermediary level challenges and so there are less prescriptive steps, and more options to solve the problem your way. For me the problem had two key parts: (1) get the latest row per staff member; and (2) the various calculations for salary and tax paid based on tax bands.

For part one I introduced an aggregage step to get the maximum row number per staff member, and then joined that back onto the input to return only the detals for that last row. I wondered if an alternative might have been to use a “level of detail” expression, with a filter, in a single step.

For part two I included all of the calculations in one “clean” step. I did hardcode the tax bands, and probably could have used a mapping table to allow for reuse in future years. I also hardcoded the sum of month [1] to [12], but perhaps could have found a way to allow for less months to be in the data set, in case the flow needed to be run mid-year.

The final week of beginner month for #PreppinData involved a bit more complexity around joins, calculations and outputs. On top of Tableau’s getting started tutorial #PreppinData has been a great way to get into Tableau Prep. I’ve invested about 12-15 hours of time and feel like I’ve got a good initial grasp of the product.

The challenge walk through provided less info on the “how”, which in some ways was quite nice as I felt more license to solve the problem my way. On the other hand I wonder if I should have made my flow less complex instead of aiming for one data set that could then be filtered down to the different outputs.

#PreppinData 2024 week covered using join types, in this case to understand which seats aren’t chosen given a seating plan and booking data.

I had a preconceived idea of the solution here, as I’m used to using LEFT OUTER JOIN in SQL and then having a WHERE clause that returns rows where the result from the right hand table IS NULL. So I was expecting to have a join and then a filter in my flow. However, Tableau Prep has some additional join types that let you return entries where there are only values in the left table, only values in the right table, or even a “not inner” join for entries only in the left or right but not in both. I gave the left only option a go and it did the job nicely! Great how you can click on the segment of the venn diagram representation of the join to select the type too.