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.
Want to try out these 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:
o Last – The previous time period
o Next – The upcoming time period
o Current – The present time period
o 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:
o Year
o Quarter
o Month
o Week
o Day
o Hour
o Minute
o 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.
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:
o Year
o Quarter
o Month
o Week
o Day
o Hour
o Minute
o Second
Length (Optional) – Allows for the time period to be shifted forward (positive numbers) or backward (negative numbers)
Offset (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 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.
FAQs
How can I make a dynamic today offset?
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.
I’m getting a null or error with my aggregate date range function. What’s happening?
This content was originally posted on phData’s website. Click here to read the original post