Hi all,
I'm trying to draw up some formulas to answer the questions "How many constituents attended 1 performance this year?" and "What percentage of guests this year attended only 1 performance?", then scaling up to two perfs, three, and so on. I think I've found an answer, but I want to double-check.
SUM ( [Constituent ID] , IF( ([# of Attended Perfs | 9.19.22 v1]) = 1 , 1 , NULL ))
/
[# of unique Constituent ID]
Those numbers don't add up to 100, which is irritating, but I suppose that's because not all guests who reserved tickets in a given year (and therefore would be tagged as 'in' that production season) would have attended. There would be guests who reserved tickets, but didn't attend. Does that make sense?
When I add in a column for '0' performances attended, it comes out to 0% each year. This is when I use # of performances = 0, and ISNULL(# of performances).
ex)
SUM ([Constituent ID] , IF( ISNULL ([# of Attended Perfs | 9.19.22 v1]) , 1 , NULL ))
Is there an easier way to do this? Does this seem correct?
Hi Nathaneal,
The first thought is to move to a constituent ID pivot table filtered to just one of the years. Then use [# of unique Performance ID] as one value, and your [# of Attended Perfs | 9.19.22 v1] as a second value, and sort it ascending by your formula to see what the distribution of distinct values look like across your constituents. This could help verify that those non-attending constituents have a NULL for your formula.
If I find a 0-value constituent row in this second (checker) widget, then filter the first widget by that constituent ID, the first widget should show '1' in the '0 attended perf' column, right? For instance:
However, it doesn't show up in the '0 attended perf' column in the first widget.
2627 shows up under the '1 perf only' (not attended), but not under the '1 perf only' (attended) column. When I look up 2627's record, it shows
The widget should show 100% under the '3 attend' column for 2021 (which it does), and 100% under the '1 attend' column for 2022 (which it doesn't). It shows 1 performance (total), but no attended total.
2627 is the household, the attendance for 2022 was under the individual's account (80393). That would explain why 2627 had 1 performance (that was correct), but the three attendance was under the sub-account. That makes sense!However, I still don't understand where the other constituent IDs are.