how to define full quarters
I'm using this formula to calculate the average quarterly engagements
SUM(Total Engagements
) / COUNT(DISTINCT QUARTER(Date
) )
When a marketing campaign data falls under full and partial quarters, however, this approach generates a skewed number.
I need a way to test for and countthe number of full quarters that a campaign runs so that I can calculate average quarterly engagement based that instead.
Thanks in advance for the help!
Best Answer

Okay is the total engagements field populated every day during the duration of the campaign? If so, this is how I did it:
ETL overview:Step 1:
Add Quarter and Year if not already in your data
Step 2:
Use 2 group by tiles to get the minimum date and maximum date for each campaign within each quarter. Minimum is shown below. Maximum is the same except you choose 'Maximum' as the aggregation type instead of minimum.Step 3:
Join the data so that for each row you have the minimum and maximum dates
Step 4:
Create a field using the formula tile:
CASE
 Q1
WHEN Quarter = 1 AND DAY(Minimum Date) = 1 AND DAY(Maximum Date) = 31 THEN 'Full Quarter'
 Q2
WHEN Quarter = 2 AND DAY(Minimum Date) = 1 AND DAY(Maximum Date) = 30 THEN 'Full Quarter'
 Q3
WHEN Quarter = 3 AND DAY(Minimum Date) = 1 AND DAY(Maximum Date) = 30 THEN 'Full Quarter'
 Q4
WHEN Quarter = 4 AND DAY(Minimum Date) = 1 AND DAY(Maximum Date) = 31 THEN 'Full Quarter'
ELSE 'Partial Quarter' ENDStep 5:
Join this data back to your base data
Step 6:
Filter out 'Partial Quarter' in your card
This is what the data would look like for reference:
If I solved your problem, please select "yes" above
7
Answers

Could you clarify what you mean by full and partial quarters? In your beastmode you only use a single date which would always fall in a "full quarter". Do you need to use a range of dates, meaning your campaign started on one date and ran through to another date and you wanted to only count those that ran from the very start to very end of quarter? More information would be helpful.
If I solved your problem, please select "yes" above
0 
One campaign, for example, launched on 12/20 of last year and will complete at the end of this year. It only ran a few days in Q4 FY22 and Q2 FY23 isn't over yet, so I'd want to exclude those two quarters.
0 
Okay is the total engagements field populated every day during the duration of the campaign? If so, this is how I did it:
ETL overview:Step 1:
Add Quarter and Year if not already in your data
Step 2:
Use 2 group by tiles to get the minimum date and maximum date for each campaign within each quarter. Minimum is shown below. Maximum is the same except you choose 'Maximum' as the aggregation type instead of minimum.Step 3:
Join the data so that for each row you have the minimum and maximum dates
Step 4:
Create a field using the formula tile:
CASE
 Q1
WHEN Quarter = 1 AND DAY(Minimum Date) = 1 AND DAY(Maximum Date) = 31 THEN 'Full Quarter'
 Q2
WHEN Quarter = 2 AND DAY(Minimum Date) = 1 AND DAY(Maximum Date) = 30 THEN 'Full Quarter'
 Q3
WHEN Quarter = 3 AND DAY(Minimum Date) = 1 AND DAY(Maximum Date) = 30 THEN 'Full Quarter'
 Q4
WHEN Quarter = 4 AND DAY(Minimum Date) = 1 AND DAY(Maximum Date) = 31 THEN 'Full Quarter'
ELSE 'Partial Quarter' ENDStep 5:
Join this data back to your base data
Step 6:
Filter out 'Partial Quarter' in your card
This is what the data would look like for reference:
If I solved your problem, please select "yes" above
7 
Thanks so much!
0 
Love @colemenwilson approach! I might be overthinking it but there is a slight change I would make to Step 4, since currently the formula would return Full Quarter for the following scenarios for example:
 something that only has entries from 1/1 to 1/31, since it is Q = 1 starting on a 1st and ending on a 31st.
 something with entries from 6/1 to 6/30, since it is Q = 2 and starts on a 1st ending on a 30th.
So you might want to check also the Month of the
Minimum Date
andMinimum Date
to ensure is taking a full quarter.Just keep in mind that as he stated, you need to have an entry for each campaign day even when there are no engagements, so you might need to create additional entries first to ensure this. Also Quarter returns a single 14 number, so if you have campaigns that might span for more than a year, you might want to do something like for your Distinct Count:
(QUARTER(`Date`)1)+4*YEAR(`Date`)
Which returns a unique number that represents each quarter.
@cthtcc, something you might want to think about, since the final goal is to show average quarterly engagements, is how should your report work when a campaign has not been active for at least 1 full quarter? Lets say it launched on 1/20/2023 and we're on 6/29/2023, so it has not been active for a single full quarter, although it has been active for more than 5 months. In your current definition that would be 0 full quarters which will error out when trying to do the division. You could consider that each quarter has on average 92 days, so you can prorate.
2 
@marcel_luthi Nice catch! Yes your additional logic is needed.
If I solved your problem, please select "yes" above
0
Categories
 All Categories
 1.5K Product Ideas
 1.5K Ideas Exchange
 1.4K Connect
 1.1K Connectors
 286 Workbench
 4 Cloud Amplifier
 5 Federated
 2.7K Transform
 91 SQL DataFlows
 580 Datasets
 2.1K Magic ETL
 3.5K Visualize
 2.4K Charting
 624 Beast Mode
 29 App Studio
 33 Variables
 613 Automate
 156 Apps
 422 APIs & Domo Developer
 33 Workflows
 2 DomoAI
 29 Predict
 12 Jupyter Workspaces
 17 R & Python Tiles
 373 Distribute
 104 Domo Everywhere
 266 Scheduled Reports
 3 Software Integrations
 101 Manage
 98 Governance & Security
 4 Domo University
 15 Product Releases
 Community Forums
 37 Getting Started
 28 Community Member Introductions
 91 Community Announcements
 4.8K Archive