Hello All,
I was hoping someone could help. I am struggling with getting the correct percentage of the New to File. Below is the formulas I am using, though the percentages come out about double of what they should be.
How I find the New to File:
COUNT ( [Constituent ID], IF ( MIN( DDIFF ( [Days in Date] , [Days in First Performance Date] ) ) = 0 , MAX ( [Constituent ID] ) , NULL ) )
The closest I have come to getting the percentage of New to File:
SUM ([Ticket Count],COUNT ( [Constituent ID],
IF (
MIN( DDIFF ( [Days in Date] , [Days in First Performance Date] ) ) = 0
, MAX ( [Constituent ID] )
, NULL
)
) ) /(SUM([Total Ticket Count]))
Example: Total Ticket Count: 1596, New to File: 176, New to File %: 22.1% (it should be about half of this)
Are you looking for a % of constituents that are new to file, or a % tickets attributable to constituents that are new to file? For the first...
COUNT ( [Constituent ID],IF (MIN( DDIFF ( [Days in Date] , [Days in First Performance Date] ) ) = 0, MAX ( [Constituent ID] ), NULL)) / [# unique Constituent ID]
COUNT ( [Constituent ID],
) / [# unique Constituent ID]
For the second...
SUM ( [Constituent ID],IF (MIN( DDIFF ( [Days in Date] , [Days in First Performance Date] ) ) = 0, [Total Ticket Count], NULL)) / [Total Ticket Count]
Hi Chris,
Thank you for responding. I am looking for a percentage of constituents that are new to file per performance.
Great. Then that first formula should get you there. The count of constituents that are first timers divided by the total count of constituents.
Cheers,Chris
I wish it did.. If you do the math from Total Ticket Count, the percentage is double what it should be.
I'm sorry, I'm confused Bobbie. You said above that you're looking for a percentage of constituents that are new, but you also said to do the math from Total Ticket Count. Indeed, dividing 176 constituents by 1,596 tickets is 11%, but that's a mixed percentage? The numerator and denominator of our percentage both should either be tickets or be constituents. I don't see a total constituent count on there, but my guess is that 176 divided by that number will be 22.1%.