Hello all, this seems like such a basic filtering question but I am still very new to Analytics and catching on to all the various widget filtering options. Is there a way to filter widgets based on a "does not have" query similar to lists? In my case, I'm looking to generate a count of donors who made a qualifying FY24 contribution (above $X, in the FY24 campaign) and did not have a qualifying FY23 contribution (below $X or non-existent, in the FY23 campaign). Thanks in advance!
My first thought is to create two formulas
One like "SUM(Contribution Amount) , [year = 2024], [campaign = X]", then filter the widget by that > $Y (Y is the amount you set).
Set one for 2023 like "SUM(Contribution Amount) , [year = 2023], [campaign = X]" and set the filter for the widget to >= $Y.
Then you'd have a 'Row' of 'Constituent Display Name' and 'Constituent ID', a 'Value' for '# of Unique Constituent ID', and 'Grand Total' the first Row.
That Grand Total (the sum of the number of Unique Constituent IDs) will be the number of people who meet both those criteria:
You could even pull 'Campaign' out of the bucketed formula and put it as another widget filter, if you wanted to. I leave that up to you! OR
You could set up a list for everyone who meets those criteria, and filter the widget for everyone NOT in that list.
Do those help? I can walk through the specifics if you need!
^ I second the suggestion of a list. You *can* get Analytics to do stuff like this, but it's often cleaner and easier (and more bullet-proof) to do this kind of complex filtering in List Manager and then simply point Analytics to your list.
A caution: Dynamic Lists are not automatically regenerated prior to each Analytics load. If you figure out the times on your Analytics full and incremental loads (often different times when set up by Hosted Services) you can set up Execute an Output Set to force a regeneration. And looking forward, I did file this Idea:
community.tessituranetwork.com/.../analytics-should-honor-dynamic-lists