Whilst working on a Workout Wednesday, we came across a challenge that took a while to get the mind around it. If you ever needed to get a second value in the date range and did not know how - well here is how!
This example will use the Superstore Dataset so that you can follow along too.
We will find out the second order that a customer made, which is useful in many ways. This way you can check the time it took for a customer to make another purchase, how often do they make purchases etc.
This will use Level of Detail Calculations. As a reminder, a calculation that is within the {} is fixed to a specific field you choose to fix it by or all of them if you do not fix it to anything in particular. There is also a blog written by me as an Intro to LODs, if you want a refresher, check that out too. The link is here: The Data School - Intro to LODs.
In this instance, the LOD will be fixed on Customer Name as we want to find the date of the second purchase, and we will do this in two steps.
Firstly, we need to find the date of the first purchase. This step is pretty easy. This is how the calculation should look like:
As an explanation, this takes the minimum date of each customer. This is the first date they have made a purchase. This will give back a date field.
Now for the second step, this will be the harder calculation. This is how the calculation should look like (with added comments):
Let's break it down. At first, focus on the IF statement. Here, we want to know when the second order is made. So, we need the Order Date to be greater than the date of the first purchase. Hence: IF [Order Date] > [First Purchase] THEN [Order Date] END.
This will give us the second order date. As for the second part, the IF statement wrapped in MIN is that we receive the lowest date from the IF statement and to make sure it is aggregated. Then, as before, we fix it on the customer name to get the specific date of the second order for each customer.
And there you have it! This is how you calculate the second value in a date range using LODs. This will be useful in any sort of analysis too as there are many different calculations that can be changed in an LOD and also you can use different fields to fix it by!