If you want to create a summary table based on records within a specified date range (or several different date ranges), you can do so using a formula column.
In your table, create a new column to contain your formula. This formula will return True if the date falls within the given date range and False if it does not. You can also modify the formula to return more specific details - which I show towards the end of the example. Then, you can create a summary table grouped by this new column to summarize your data.
I used the Credit Card Activity Template to demonstrate this.
In this first example, we simply assign dates to variables then compare these dates to see if the given Date for this record falls within the given ranges.
The formula we use is:
date_1 = DATE(2022, 6, 1)
date_2 = DATE(2022, 7, 1)
date_3 = DATE(2023, 1, 1)
date_1 <= $Date <= date_2 or date_3 <= $Date <= TODAY()
First, we take each date we want to compare and assign it to a variable. Note that you must use the DATE() function to convert each given date into a datetime object.
Then, we write our comparison. In this example, we use an or comparison. So, if the value in the Date column falls between 06/01/2022 and 07/01/2022 or it falls between 01/01/2023 and TODAY(), then the formula returns True. If the value in the Date column does not fall between either of these date ranges, the formula returns False. We can then create a Summary Table grouped by this new column, date range, to view how many transactions occurred within (or outside of) the given date ranges and the sum of the amounts for those transactions.
If you wanted to take this a step further, you can return text that specifies which date range the given Date falls within.
To do this, you could modify the formula similar to what you see below:
date_1 = DATE(2022, 6, 1)
date_2 = DATE(2022, 7, 1)
date_3 = DATE(2023, 1, 1)
if date_1 <= $Date <= date_2 or date_3 <= $Date <= TODAY():
if date_1 <= $Date <= date_2:
return "Falls within first range"
if date_3 <= $Date <= TODAY():
return "Falls within second range"
If the Date satisfies the first range, you can return a string stating that. If the Date satisfies the second range, you can return a different string. Then, your summary table would also be split to show you sums for records that fall within the first range, the second range and those that do not fall within the given date ranges.
