Automation issue

Bannoradjaye

I have a sheet that has a number of metrics for which I request and report data for on a number of different cadences i.e. monthly, quarterly etc. across the entire year.

I have the following fields in the sheet: Metric name, Data Reporting Frequency (which can be Monthly, Quarterly etc.), Reporting Date (this is the date for which I want the metric data for), Name of Month (this take the name of the month of the "Reporting Date" field), Pass Date (helper field used in the automation) and Next Update.

What I'm trying to achieve is this : when a month ends for example June 2024, I want the automation to send out an email request on the first day of July requesting for the previous months data (i.e. June), the same for the prior months i.e when May ends, I want the automation to send out an email request on the first day of June (which is the next month) requesting for the previous months data (i.e. May).

I think I have been able to setup the automation however when May ended and the automation was triggered rather than requesting for May data it requested for April data. The same thing happened in April where rather requesting for April data in May it requested for March data. Below is a screenshot of the sheet and the automation. The "Pass Date" field checks to see if the current date is greater than the "Date" field. If yes then when the automation is triggered it should request for data for the date in the "Date" field. In the table below the automation should ask for data for May, however it asked for data for April. Thanks in advance.

iLoveData

@Bannoradjaye

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!

iLoveData

@Bannoradjaye

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!

Bannoradjaye

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.

iLoveData

@Bannoradjaye

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")

Bannoradjaye

@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.

iLoveData

@Bannoradjaye

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")

Bannoradjaye

@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.

iLoveData

@Bannoradjaye

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?

Bannoradjaye

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

Paul Newcome

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

Bannoradjaye

@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?

Bannoradjaye

@Paul Newcome the quarters are calendar quarters. Thanks.