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.
I'm guessing that we might need this 0 attended bit...
IF( ISNULL ([# of Attended Perfs | 9.19.22 v1])
to instead be...
IF( [# of Attended Perfs | 9.19.22 v1] = 0
or to cover all bases...
IF( ISNULL ([# of Attended Perfs | 9.19.22 v1]) OR [# of Attended Perfs | 9.19.22 v1] = 0
This may sort out constituent 2563.
The negative constituent ID is a general publish admission not tied to a known constituent. In fact, we should give these a bit of thought. There's a placeholder, negative constituent ID for each distinct post code order survey response. Since those represent post codes instead of unique constituents, they should probably not be included in the analysis - at all. I recommend removing them with a widget or dashboard filter on CONSTITUENT.Type.
Even with the 'OR' for the %0 column, it still doesn't show any data for 2627.
SUM ( [Constituent ID] , IF( ISNULL([# of Attended Perfs | 9.19.22 v1]) OR ([# of Attended Perfs | 9.19.22 v1]) = 0, 1 , NULL ) )
Since 2627 (the household, not the individual) has 0 attended performances in 2022, it should show up as 100% in that column. 2021 has 3 attended perfs, so 2021 has 100% in the 3 column. 2022 has 0 attended perfs, so it should show up in the 0% column.2563 is in the same boat- they have one attended performance, but under a sub-account, not on the household. They, too, should show up under the 0 column, shouldn't they?
Try something... change
, 1 , NULL
to
, SUM( 1 ), NULL
or MAX( 1 )
in this statement: IF( ISNULL([# of Attended Perfs | 9.19.22 v1]) OR ([# of Attended Perfs | 9.19.22 v1]) = 0, 1 , NULL )
The MAX(1) seems to work. The SUM(1) gives inflated numbers:
I've gone back and changed all the formulas to SUM(1), and the numbers are as follows:
three of the years are still off, but by less than .1%. I'll take that as a win! Thanks!What was the logic behind ..., SUM(1) vs ..., 1?