InDateRange formula for custom PoP analysis
1. InDateRange formula for custom PoP analysis
Great job so far learning to navigate Sigma’s period-over-period comparison menu. In this video we’ll discuss what to do when we have more complex comparisons, such as using rolling or non-standard timeframes. Let’s look at an Oakmark Bank example. Here’s a table of daily transaction counts at Oakmark Bank. An executive wants to see transaction volume for the past 30 days from today and they’d like to compare this number to the 30-day period before that. What if “today” is in the middle of the calendar month? The Pop comparison menu would fall short here, as it's limited to calendar-based timeframes like full months. Instead, let’s create a new column on the table with a formula flagging the rows corresponding to the most recent 30 days, and then the 30 days before that. Sigma’s InDateRange function is perfect for this. One quick note before we begin - throughout this video and its accompanying exercises, if we need to reference today’s date, we’ll be using this field, called ‘Today’s Date for exercise’. We’ve hard-coded this date into the demos and exercises, so everything will run smoothly regardless of the date you actually take this datacamp course. Please do not edit or change this date value. The InDateRange function requires several parameters. As you begin typing in the formula bar, remember that Sigma prompts you with hints on the parameters required and how to complete them. We’ll start by pointing to the date column we’re testing – [Day] in this case. Next comes direction, which can either be ‘last’, ‘next’, ‘current’ or ‘to-date’. We’re looking for the most recent 30 days, so ‘last’ works here. Next comes the period, or unit of time we’re looking back. Length will be 30 for 30 days, and our offset can remain at zero. Offset is optional, but by leaving the default we’re asking to include the current period we’re in right now - in this case, today’s date. Normally we could end the formula here, but because we’re using a special date for today’s date, we’ll fill in an offset of zero and then reference our exercise date for today’s value. InDateRange functions return logical, or True/False values indicating whether each row is within the most recent 30 days. Let’s create a second column flagging the comparison period, or days 31-60 days prior to today’s date. We’ll use InDateRange again, but this time we’ll specify an offset of 30 days so the formula begins counting the most recent 30 days only after backing up - or offsetting - 30 days from today’s date. Flagging these dates is a great start, now let’s display the percent change between the two time periods with a KPI chart. Our first step will be to make use of table summaries to count the number of transactions in each 30-day group. We can create one table summary that sums transaction counts for the past 30 days, and one for days 31-60. Remember that because InDateRange produces logical columns, a SumIf function here will only sum the rows where the result was True. We’ll create a third table summary to calculate the percentage change between the two timeframes and format it as a percentage, naming it ‘% change in tx volume’. We can finish by turning this summary into a KPI chart from the summary drop-down. Now Oakmark has a custom calculation for the most recent 30 days, no matter what day “today” is. InDateRange can be used for many purposes, but it’s especially powerful when your comparison needs rolling or non-standard timeframes. Now its your turn to practice with the InDateRange functions for period-over-period comparison.2. Let's practice!
In the next exercises, you’ll be hands-on with the InDateRange function to solve a number of different use cases for the Oakmark Bank teams. Enjoy.Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.