Automation issue

Hello,

I would set change the Name of Month column to Month # and have the formula be = MONTH([Name of Month]@row) to return the month value then set up another column Month -1 = MONTH(TODAY()) -1 then Change the Pass date from a checkbox to a formula being = IF([Name of Month]@row = [Month -1]@row, "Previous Month", "N/A")

This would allow you to dynamically determine if the month is 1 month previous to the start of the automation. I'd do this because the automation begins on the first of the new month you want it to dynamically filter out all months that came before by labelling them "N/A".

I hope this helps!

All comments

Hello,

I hope this helps!

Thanks @iLoveData. I have implemented it. Currently I have this setup for two automations one for metrics with "Monthly" reporting and another for metrics with "Quarterly" reporting (I haven't specified if it is calendar or fiscal quarter). Is there a way to tweak the automation to ensure that it runs only on either a calendar or fiscal quarter basis for the metrics with "Data Reporting Frequency" - "Quarterly"? Thanks.

No problem! For fiscal quarter, assuming the date column is the date you want the metric to run for, you can adjust the Month -1 to be quarter -1 (Month -3) so at the start of the new quarter you can check the previous beginning of quarter month against the current month -3 to see if they match.

example being month = Jan 31st, today is april 1st you'd test the month of your month column or quarter column against the current day in a quarter -1 column. Example = MONTH(01/31/24) = MONTH(TODAY())-3. Just make sure you have separate columns or specific values in your existing columns to differentiate between the quarterly and monthly.

You can nest both of the formulas in an IF statement to keep the formula all in one line as well: Example =IF([Data Reporting Frequency]@row = Monthly, IF([Name of Month]@row = [Month -1]@row, "Previous Month", "N/A"), IF([Start of Quarter]@row = [Quarter -1]@row, "Previous Month", "N/A"),"N/A")

@iLoveData Where is the "Start of Quarter" field coming from and is it a date field? It looks like it is a new field I have to create. I'm not sure I understand how to do that. I'm thinking for the quarterly labelled metrics, I change the Date field formula to =DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 62 for Quarterly and for monthly it will be =DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1.

I would do it in a new column or in the date field for quarterly reports, whatever works best for your sheet; however, doing quarter by -62 will only be slightly accurate as not all months will have 31 days. I would use a relative date formula like this (see below) to determine if the current date falls within the start of a new quarter and then use that as a trigger for the quarterly analysis

=IF(OR(MONTH(TODAY()) = 1, MONTH(TODAY()) = 4, MONTH(TODAY()) = 7, MONTH(TODAY()) = 10), "Start of New Quarter", "No Analysis Needed")

@iLoveData so I created a new field "Quarter" to give me the quarter of the "Date" field. I also created the "Start of Quarter" field but currently returning "No Analysis Needed" since we are in June and that makes sense. I incorporated the formula (= IF([Name of Month]@row = [Month -1]@row, "Previous Month", "N/A")) in the Pass Date field. What I haven't been able to figure is how to dynamically change the metrics with quarterly dates in the "Date field" from 3/31/24 to 6/30/24 etc. Also not sure if this formula (=IF([Data Reporting Frequency]@row = Monthly, IF([Name of Month]@row = [Month -1]@row, "Previous Month", "N/A"), IF([Start of Quarter]@row = [Quarter -1]@row, "Previous Month", "N/A"),"N/A")) is an "if else" statement or "if and" and how the "Start of Quarter" field works in the formula since its output i qualitative and not numerical (since it is being compared to [quarter-1]@row.

Thanks.

For dynamically changing the metrics with quarterly dates, I'd use a nested if statement to determine the current month against the date ranges of each quarter:

=IF([Name of Month]@row = 1, 1, IF([Name of Month]@row < = 4, 4, [Name of Month]@row < = 7, 7, IF([Name of Month]@row < = 10, 10, 1)))

Regarding the previous formula, that's now outdated based on the new calculation used for quarter. With the qualitative response from the quarter field you don't need to pass it through the pass date column if you don't want. I'd just recommend setting up the automation to run based on pass date for monthly and start of quarter for quarterly. Just make sure to filter based on Data reporting frequency for monthly and quarterly respectively

Paul Newcome

What exactly are all of your different frequencies?

@Paul Newcome my frequencies are Monthly, Quarterly, Semi-Annually and Annually.

Paul Newcome

And would "Next Update" be driven by the "Date" column?

@Paul Newcome that is correct. Date field formula is [=DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1] for "Monthly" frequency and [=DATE(YEAR(TODAY()), MONTH(TODAY()) - 3, 31)] for quarterly frequency . "Next Update" field formula is [=Date@row + 30]

Paul Newcome

And are your quarters calendar quarters or fiscal year quarters that have a different start/end?