Filter and show data only from this quarter

Need help Database
1

I have a table that contains the “Revenue” column.
I need to show a SUM of Revenues only of this quarter.
I need a filter that would dynamically adapt to quarter and to the current year.

2

I haven’t tested this, but this should work:

  1. Create an option set called Quarter, add an attribute for start_date and end_date both of type date.
  2. Add options Q1, Q2, Q3, Q4.
  3. Add their attributes for the start and end dates of their respective quarters. (any year will do as we’ll see in steps 7 and 8).
  4. On your page add a custom state for Current_Quarter of type Quarter.
  5. Add another custom state called Quarter_Date_Range of type date range.
  6. On page load set Current_Quarter to the actual current quarter as follows by creating 4 actions:
    1st: Set Current_Quarter to Q1
    2nd: Set Current to Q2 only when current date’s month >=4
    3rd: Set Current to Q3 only when current date’s month >=7
    4th: Set Current to Q4 only when current date’s month >=10
  7. Create another action after step 6’s action to set Start of Quarter_Date_Range to Current_Quarter’s start date and then another action immediately after to change years to current year. Something like this.
    Screen Shot 2020-10-21 at 4.45.01 AM
  8. Repeat step 7 for Current_Quarter’s end date.

You now have a Current_Quarter date range that you can run your revenue reports on.

2 Likes