Rolling Fiscal/Quarterly Counts?

Jen Castillo

How would I go about populating the number of something happening within a certain Quarter or Fiscal Year? In this instance, I want to count the number of Cross-Dock orders that happened in the last quarter, but then also for the entire fiscal year.

Another example of how I'm trying to use this is that I'm trying to gather all of the orders we had into a pie chart to show percentage of each type, but I'd like to only have this reference the relevant fiscal year orders without having to update it manually.

My first thought was using countif criteria to have "Q_" or the fiscal year since I have other columns generating quarter/fiscal year data, but then I'd have to go in and change my formula for each quarter or fiscal year change.

Ideally, this would automatically continue into the next quarter/fiscal year without me touching anything. Our FY is OCT-SEPT.

Thank you in advance, and have a happy weekend!

Jen

Paul Newcome

This will generate the date for the first of the current fiscal year. You can use this as part of the criteria in your other functions as the start date:

DATE(YEAR(TODAY()) - IF(TODAY() <= DATE(YEAR(TODAY()), 10, 01), 1, 0), 10, 01)

The last day of the current fiscal year would be:

DATE(YEAR(TODAY()) + IF(TODAY() >= DATE(YEAR(TODAY()), 10, 01), 1, 0), 10, 01) - 1

Paul Newcome

This will generate the date for the first of the current fiscal year. You can use this as part of the criteria in your other functions as the start date:

DATE(YEAR(TODAY()) - IF(TODAY() <= DATE(YEAR(TODAY()), 10, 01), 1, 0), 10, 01)

The last day of the current fiscal year would be:

DATE(YEAR(TODAY()) + IF(TODAY() >= DATE(YEAR(TODAY()), 10, 01), 1, 0), 10, 01) - 1

Jen Castillo

https://community.smartsheet.com/discussion/comment/340060#Comment_340060

Hi Paul! Thanks so much for your response. Could I ask for a bit more clarification?

The formula I'm trying to use this criteria for is:

=COUNTIFS({Inspection Source}, "Projects", {Inspection Complete}, >=TODAY(-30))

but instead of the "last 30 days", I tried:

=COUNTIFS({Inspection Source}, "Projects", {Inspection Complete}, DATE(YEAR(TODAY()) - IF(TODAY() <= DATE(YEAR(TODAY()), 10, 01), 1, 0), 10, 01)

The DATE/YEAR/TODAY functions always send me for a loop! 😔I'm trying to do the same thing with my quarters as well.

Jen Castillo

https://community.smartsheet.com/discussion/comment/340060#Comment_340060

I thought I replied already but I don't see my comment... Thank you for your response! Could I ask for more clarification? Using TODAY/DATE/YEAR flummoxes me.

The formula I'm trying to make this a criteria in is:

=COUNTIFS({Inspection Source}, "Projects", {Inspection Complete}, >=TODAY(-30))

Instead of the "within the last 30 days" I've got written, how would I plug your above criteria in so that I count everything within the last fiscal year? How would I do that with something quarterly as well?

Thank you for your patience!

Paul Newcome

Here's a breakdown:

DATE(YEAR(TODAY()) - IF(TODAY() <= DATE(YEAR(TODAY()), 10, 01), 1, 0), 10, 01)

DATE(yyyy,mm,dd) outputs a usable date either directly in a cell or as a date to be further used in a formula.

TODAY() simply implies today's date.

The YEAR function strips the year from whatever date is referenced, so

YEAR(TODAY())

is basically saying "current year".

Now we have

DATE(current_year, mm, dd)

But if today's date is before Oct 1, then we need to subtract a year.

DATE(YEAR(TODAY()) - IF(TODAY() <= DATE(YEAR(TODAY()), 10, 01), 1, 0), 10, 01)

IF(today's_date is less than or equal to Oct 1 of the current year, 1, otherwise zero)

Now we have

DATE(current_year minus IF(today's_date is less than or equal to Oct 1 of the current year, 1, otherwise zero), mm, dd)

All that is left is to add in the 10 and 1 for month and day output by the main DATE function and you will have the start of the current fiscal year.

Similar logic is used to determine the last day of the fiscal year where we use the same functions but we add 1 to the year (when necessary) and output 1 Oct. Subtracting 1 from that final date will give us the very last day of the previous month.

So if you want to count for current Fiscal year, you would use an AND statement as the criteria in your COUNTIFS when evaluating the date range.

=COUNTIFS({Inspection Source}, "Projects", {Inspection Complete}, AND(@cell >= start_of_fy_date, @cell <= end_of_fy_date))

=COUNTIFS({Inspection Source}, "Projects", {Inspection Complete}, AND(@cell >= DATE(YEAR(TODAY()) - IF(TODAY() <= DATE(YEAR(TODAY()), 10, 01), 1, 0), 10, 01), @cell <= DATE(YEAR(TODAY()) + IF(TODAY() >= DATE(YEAR(TODAY()), 10, 01), 1, 0), 10, 01) - 1))

Jen Castillo

https://community.smartsheet.com/discussion/comment/341755#Comment_341755

I kept getting pulled off the project, so what took you a few hours to respond took me DAYS to process and understand, LOL. Thank you so much for your breakdown. It worked perfectly! I vote Paul Newcome for the member spotlight of August until the end of time.

I can definitely post a new discussion (since you've already helped me so much and I don't want to 'overstay my welcome') but my last hurdle is that I'm trying to keep a rolling quarterly formula as well. I figured out a way that works, but is there a more efficient way to do this using the above methods?

Currently I have a column in my main document that generates the quarter with:

=IF(MONTH([Receiving Completed Date]@row) <= 3, "Q2", IF(MONTH([Receiving Completed Date]@row) <= 6, "Q3", IF(MONTH([Receiving Completed Date]@row) <= 9, "Q4", "Q1")))

And then on a separate metric sheet generating numbers for my dashboards, I've got:

[Column9]137 is generating a reference for the current quarter:

=IF(MONTH(TODAY()) <= 3, "Q2", IF(MONTH(TODAY()) <= 6, "Q3", IF(MONTH(TODAY()) <= 9, "Q4", "Q1")))

[Metric] is counting from my main document based off my [Column9] input.

=COUNTIFS({Main Doc Inspection Source}, "Cosmo", {Main Doc QTR}, =[Column9]137)

So it works, but there must be a better way!

Again, thank you so much!