Connect offers the possibility of filtering date columns in two ways: Static and Dynamic. What is the difference?
Dynamic options
They allow you to filter:
- By calendar period (e.g. Last calendar year, Next calendar year, Current year)
- By moving period (e.g. Last/Next moving year, Last/Next 3 moving months)
- Period-to-date (Year to date, Quarter to date, Month to date, Week to date)
You can select one of the predefined suggested options or go to custom mode for more flexibility and apply criteria such as Last 2 moving years, Next 5 calendar months, etc.
What does each option mean?
- a calendar week is a period from Monday to Sunday
- a moving week is a period of 7 days counting from today. Example: if today is Wednesday
- Last moving week means period from last Wednesday until yesterday
- Next moving week means from tomorrow until next Wednesday
- a calendar month is a period from 1st to last day of Jan/Feb/Mar, etc.
- a moving month is calculated by adding or subtracting one month from current date. If today is Feb 26:
- Last moving month is from Jan 26 to Feb 25
- Next moving month is from Feb 27 to Mar 26
- a calendar quarter is one of the four periods: Jan 1st - Mar 31, Apr 1st - Jun 30, Jul 1st - Sep 30
- moving 3 months are calculated by adding or subtracting 3 months from current date. If today is Feb 26:
- Last 3 moving months means 26 Nov - 25 Feb
- Next 3 moving months means 27 Feb - 26 May
- a calendar year is a period from Jan 1st to Dec 31
- a moving year is a period of one year calculated by adding or subtracting from current date. If today is Feb 26 2025:
- Last moving year is the period from Feb 26 2024 to Feb 25 2025
- Next moving year is the period from Feb 27 2025 to Feb 26 2026
If it’s not 100% clear to you, do not worry, the hint displayed below the filtering criteria will always let you know what exact period your selection refers to as per the day of the setup (tomorrow it will be different, and this is what makes dynamic filtering great).
Available filtering options by column granularity
Availability of granularity levels depends on column granularity (whether the data shown is grouped daily, weekly, monthly, quarterly or yearly), taking into account its modification by the user, if it has been applied. It means that, for example, a column showing the data aggregated monthly will never allow filtering with weekly or daily granularity (Last 3 weeks, Next 45 days, etc.), to avoid unclear results or impossible filtering criteria.
What about the columns that don’t have very recent data?
As you know, in most of our past-looking smarts we offer data with a few days (or more, depending on the smart) of delay, meaning that the current date is usually not available. It has no impact on the filtering criteria available for Connect users, although, of course, it will impact the results you will get.
For example, if I select “Week to date” option for my past-looking column, I will get no results on Monday and Tuesday for sure, but on Friday I may start seeing a few rows of data.
Static options
They allow you to select an operator (Starting, After, Ending, Before, Between, is or is not) and a date or a date range from the calendar. In this case the filtering criteria remains unchanged unless you go back to your custom view and modify the filter.
These options do not give the same flexibility as dynamic ones, but there are plenty of use cases that require using static dates, such as analysing performance in holiday periods in different years to compare them, checking forecasts for the specific dates when some important local events happen, etc.