In data analytics, time period comparisons are a powerful tool for identifying trends and making informed decisions. However, performing these comparisons can be challenging, especially when dealing with large datasets or complex date ranges.
Fortunately, Sigma Computing offers a range of functions that make it easy to determine if a date falls within a certain range, enabling users to quickly and easily perform time period comparisons. By using these functions, users can gain valuable insights into their data and make data-driven decisions with confidence.
In this blog, we’ll explore how to use these functions in Sigma and provide some tips and best practices for making the most of this powerful feature. Whether you’re an experienced data analyst or just getting started with data analytics, this guide will help you unlock the full potential of Sigma’s time period comparison functions.
What is InDateRange?
InDateRange returns True/False to identify if a date is within a certain time period as defined by the arguments.
Arguments:
- Date – This is the date to evaluate if it’s within the time period
- Direction – How should the time period be offset? The available options are:
- Last – The previous time period
- Next – The upcoming time period
- Current – The present time period
- To_date – The present time period to today (i.e., year to date vs. a whole year)
- Period – How long is the time period? The available options are:
- Year
- Quarter
- Month
- Week
- Day
- Hour
- Minute
- Second
- Length (Optional) – Allows for showing multiple periods. Defaults to 1.
- Offset (Optional) – Allows for the time period to be shifted forward (positive numbers) or backward (negative numbers)
- Today (Optional) – Overwrite what day should be used to define what day is used to define the direction. This can be used to offset Today for a custom fiscal calendar.
What is InPriorDateRange?
InPriorDateRange returns True/False to identify if a date is within a certain time period looking back at a certain time period as defined by the arguments. It answers the question, “What is in this [Range period] last [Prior Period]? “
Arguments:
- Date – This is the date to evaluate if it’s within the time period
- Range Period – – How long is the time period to evaluate? (Must be a smaller period than the Prior Period option selected.) The available options are:
- Year
- Quarter
- Month
- Week
- Day
- Hour
- Minute
- Second
- Prior Period – How far back is the Range Period? The available options are:
- Year
- Quarter
- Month
- Week
- Day
- Hour
- Minute
- Second
- Offset (Optional) – Allows for the time period to be shifted forward (positive numbers) or backward (negative numbers)
- Today (Optional) – Overwrite what day should be used to define what day is used to define the direction. This can be used to offset Today for a custom fiscal calendar.
What are Examples of Each Function?
Identify the Current Year:
InDateRange([Date Field],"current", "year")
Identify the Current quarter
InDateRange([Date Field], "current", "quarter")
Identify the Current Year with a Fiscal Start Date of Febuary 1, 2023
InDateRange([Date], "current", "year", 1, 0, Date("2023-02-01"))
Identify the Prior Quarter
InDateRange([Day of Date], "current", "quarter", 1, -1)
Identify the Prior Quarter to Date
InDateRange([Day of Date], "to_date", "quarter", 1, -1)
Identify the Trailing 12 Completed Months
InDateRange([Month of Date], "last", "month", 12))
Identify the Trailing 12 Months (Include the current partial month)
InDateRange([Month of Date], "current", "month", 12, -11))
Identify the Same Quarter Last Year
InPriorDateRange([Day of Date], "quarter", "year")
Identify the Same Week Last Month (ie to compare week 3 of the current and previous month)
InPriorDateRange([Day of Date], "week", "month")
How Can I Use Date Range Functions for my Analysis?
Since the date range functions result in a True/False, they can be combined with any of Sigma’s AggregateIF functions; for example, SumIf, AvgIf, or CountDistinctIf. This makes it easy to find the total sum or total count for a certain time period. With Sigma’s built-in comparison feature on Single Value Elements, it’s very easy to calculate time period comparisons.
Closing
In data analytics, performing time period comparisons can be time-consuming, especially with complex date ranges and large datasets. However, with Sigma’s InDateRange and InPriorDate functions, developers can save valuable time. With these functions, users can gain valuable insights into their data and make data-driven decisions with confidence.
Are you ready to unlock the full potential of your data? Reach out to us now to discover how our Sigma Computing expertise can help you harness the power of your data and make data-driven decisions with confidence.
Frequently Asked Questions
If, for example, your data updates on the last day of every month, you may want to offset “Today” for a more user-friendly experience. To have Sigma automatically use the most recent date in the dataset, add a Table Summary for a MAX of the date field. Use this max date as Today. Learn more about Table Summaries here: Create a Summary.
One thing to keep in mind when utilizing the offset features of these date range functions is the date you want to select must be in your data. For example, if you offset your selections into the future or into a date range that has been filtered out, Sigma will return a null since there is no data that meets those criteria, so Sigma doesn’t have anything to aggregate.
