Hello!I'm creating a table which has a variety of information in it that I need for our post-concert wrap ups.The only column I can't get to work is "How many tickets first time ticket buyers purchased"I currently use the formula below for the number of first time ticket buyers, but I would also like to know how many tickets those ticket buyers purchased.
Count([Constituent ID],IF(MIN(DDIFF([Days in Date],[Years in First Performance Date]))=0, MAX([Constituent ID]),NULL))
If I change Constituent ID to ticket count, the formula no longer works.I can get the number using a different widget, but its a pain as it requires a manual filter and can only display one concert at once. And I'd like it to all be in one widget!Wondering if anyone has any ideas?Cheers, Nicola
Hi Nicola,
Switch from using COUNT at the start, to using SUM, and then switch from using MAX( [Constituent ID] ) to the value you'd like to calculate that's attributable to first timer buyers, such as [Ticket Ticket Count].
SUM ( [Constituent ID] , IF ( MIN ( DDIFF ( [Days in Performance Date] , [Days in First Performance Date] ) ) = 0 , [Total Ticket Count] , NULL ) )
Amazing thank you Chris - I knew it would be something simple I just couldn't get it right! Thank you!