Create report based on separate statuses of two different CTAs?

What if you did this - build your report based on Calls to Action and use the OR criteria between your two conditions for CTAs.  
Report on Calls to Action
Show Count if ID (to count number of CTAs)
By Account Name (this will show you the actual accounts)
Filter (A AND 😎 OR (C AND D) AND Count of ID >=2 to filter down to accounts that have both of these CTA conditions

That worked Denise thanks, although it feels like a workaround.  IMO it would be helpful to use simpler criteria logic (A and 😎 AND (C and D). Not sure if there is a way to make that possible.

Denise wrote it up before I could get to it. What you're really reporting on is the CTAs, not the Accounts. I went through a similar exercise trying to prune overdue CTA and had to find all the Accounts that had 2 or more, and then determine which one to delete based on the CTA type. For example if there is both an open business review and renewal, keep the renewal.

Sort of but not exactly.  I wasn't looking for customers that have 2 or more CTAs that meet a specific criteria (overdue). I was looking for the total number of customers by comparing two CTAs with different criteria.  They are similar, but different use cases.

My use case is trying to identify how many customers have [i]initiated the onboarding process but have not yet been completed onboarded.  Since I cannot pull from both Milestone (I have one that is set when onboarding is complete) and Call to Action object at once, I am having to look at specific CTAs themselves to see which ones are open vs closed.

This may become moot when we implement our redesign (I may trigger a milestone when customer starts the onboarding process) but I still think this could be a valid logic method in the future where two distinct sets of criteria both are true.

Denise, looking into the data a bit more deeply, I don't think your recommendation worked as we hoped.

I'm trying a different tactic, using Milestones (which is probably the better approach anyway) but similar issue with comparing two Milestones.

I have two Milestones:

Onboarding Kick-Off
Onboarding Complete

Creating a report to show me all customers with a milestone Onboarding Complete is easy.
Creating a report to show me all customers WITH a milestone Onboarding Kick-Off AND WITHOUT an Onboarding Complete milestone is proving tricky.

Do you have any suggestions there?  Or should I open a ticket with support?

In reports, you will get results based on the main area you are reporting on, Milestones.  Your criteria will select a set of Milestone records.  The trick here is that your criteria is based on the Account and whether they have or don't have specific milestones.  

Account ABC  
Milestone Onboarding Kick-Off

Account 123
Milestone Onboarding Kick-Off
Milestone Onboarding Complete

So in one step reports, the best you can do is filter down to where MilestOnboarding Kick-Off records OR MilestOnboarding Complete and sum it to the Account to see if 1 or 2 milestones of these type exist.  (this is not foolproof given that someone could accidentally say create two kick-off Milestones)

The other approach is to use a rule to set a field in CustomerInfo to track these - something like one field for kick-off and one field for complete.  Your rule could evaluate for Milestone Kick-off and write the date into the field (or a checkbox if you want but date may prove useful elsewhere).  Then you have another one that does the same for Complete.  To do your report, you would build it on CustomerInfo and use those two fields to get the reports (if milestone kick-off date is Not Null and milestone complete date is NULL would be the criteria for your second report)

This first option does not appear to work, because when I am running the report to see which accounts have kicked off onboarding, but not completed, I use the criteria as follows:

Show Me:  Count of (Milestone) Id
By:  CSM and CSM Segment
Count of Id < 2 (because I only want to see accounts that have ONE of the Milestones, Onboarding Kickoff)

A. Milestone Name = Onboarding Complete
B. Milestone Name = Onboarding Kick-Off
Advanced Logic: A OR B
1. Count of Id < 2 (because I only want to see accounts that have ONE of the Milestones, Onboarding Kickoff)

CSM A, for example has 3 Accounts where she has only the Milestone Name = Onboarding Kick-Off, but since 3 come back, that is greater than 2 and does not show up.

I am going to have to go the creating a field route, but I can't believe I'm the only person who wishes to be able to run reports that can compare two records within an object (CTA, Task, Milestone, etc) and roll it back up to the account level.

PS I don't believe I can set this Question to Answered, so you may need to.  Thanks.

Hey Jeff,

Would something like this work?

Base data: Accenture and Acme have both kick-off and completion Milestones. 3M only has the former.

          

          

Then we filter for count = 1, assuming that the completion Milestone typically wouldn't exist without the kick-off Milestone already logged. We are left with just 3M.

          

Thanks,
Manu