We've just finished up a thrilling day of Tableau Prep with Carl. During one of the exercises (Preppin' data - 2021 - week 2), there was a bit of a split amongst the cohort about how we handled the 'Days to Ship' step. Some of us used the DateDIFF function, whereas others opted to subtract the order date from the shipping date. In this particular instance, both methods actually rendered the same results, as we were only looking at the change on a day level. However, this is the only timeframe where the two are interchangeable. Therefore, it is worth getting into the habit of using DateDIFF in order to be able to handle other units of time.
So, what does the function do? DateDIFF calculates the difference between two events, using the user-specified unit of time i.e. days, months, years. The syntax for running this formula is 'DateDIFF(date_part, start_date, end_date, [start_of_week])' where we have to define the following variables:
- date_part - the time horizon we want to see the difference in
- start_date - the first event we are looking at e.g. order date
- end_date - the final event we are looking at e.g. delivery date
In the example above, you can see that we have decided to look at the number of days between the customer ordering a bike and the product being shipped.
BE CAREFUL: The 'date_part' variable follows strict rules. 'day' has been used here and we can see in the syntax example that they have used 'month'. If we were to try and capitalise or pluralise here ('Day', 'Days', 'days'), the calculation would break.
Finally, it's good to remember that variables in square brackets - [start_of_week] above - are optional. This means that they aren't necessary for the calculation to work, but can be provided for extra granularity. In this case, the start_of_week defines just that. However, this can potentially affect calculations in a way you might not be expecting, as shown in the example below.
