Formula to count issues raised this quarter and in previous quarter

hannahstayo

I am putting together a metric sheet that will count issues raised based on various dates. I already have it set up to count this month vs last month, but I need help doing the same for quarter.

In my main data set I have added a helper column to return the quarter based on the date an issue was flagged. How can I use this to do a count for this quarter and last quarter? It would need to be dynamic so that it automatically adjusts as we move into a new quarter (I don't want to have to edit the formula at the beginning of each new quarter if at all possible).

Thanks in advance!

Paul McGuinness

Hi @hannahstayo

I use this formula to automatically allocate a quarter value to rows as added via a webform

=IF(OR(MONTH([Date logged]@row) = 10, MONTH([Date logged]@row) = 11, MONTH([Date logged]@row) = 12), "Q1", IF(OR(MONTH([Date logged]@row) = 1, MONTH([Date logged]@row) = 2, MONTH([Date logged]@row) = 3), "Q2", IF(OR(MONTH([Date logged]@row) = 4, MONTH([Date logged]@row) = 5, MONTH([Date logged]@row) = 6), "Q3", IF(OR(MONTH([Date logged]@row) = 7, MONTH([Date logged]@row) = 8, MONTH([Date logged]@row) = 9), "Q4", "error"))))

Our quarters start in October but you can adjust this to suit.

Hope that helps

Thanks

Paul

hannahstayo

Hi @Paul McGuinness

Thanks for your response.

This helps with assigning a quarter based on the date, but then I'd like to be able to pull the number of issues/rows in the current and previous quarter. I've been able to do this for the month using MONTH(TODAY()) and MONTH(TODAY())-1) so that it's dynamic and I don't have to edit the formula each month. I'm looking for a way to do this with the quarters too.

Paul McGuinness

Hi @hannahstayo

To fix a point formula I would use two sheet summary fields to calculate the current and last quarter like this in the sheet performing the calculations.

Current quarter formula - again adjust to suit

=IF(OR(MONTH(TODAY()) = 10, MONTH(TODAY()) = 11, MONTH(TODAY()) = 12), "Q1", IF(OR(MONTH(TODAY()) = 1, MONTH(TODAY()) = 2, MONTH(TODAY()) = 3), "Q2", IF(OR(MONTH(TODAY()) = 4, MONTH(TODAY()) = 5, MONTH(TODAY()) = 6), "Q3", IF(OR(MONTH(TODAY()) = 7, MONTH(TODAY()) = 8, MONTH(TODAY()) = 9), "Q4", "error"))))

Last quarter formula

="Q" + IF(RIGHT([Current quarter]#, 1) = 1, 4, IF(RIGHT([Current quarter]#, 1) = 2, 1, IF(RIGHT([Current quarter]#, 1) = 3, 2, IF(RIGHT([Current quarter]#, 1) = 4, 3, ""))))

You can then use those points as references in your calculations which will then move with the dates based on the Today() element in the Current quarter

Hope that helps

Thanks

Paul