Best Of

@RayRay_H yes that's definitely a limitation and I think that's partially why they created the domo finance app (you can search for it in the appstore). Also, wanted to update you that we did follow the suggestions here and created the P&L calcs through dataflows and weren't too bad at all to do. But now it makes for a much cleaner looking P&L… basically there are no aggregations or calcs anymore at the card level… we just display the metrics and calcs together.

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 and Minimum 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 1-4 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.

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' END

Step 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: