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!
Hi Chris,
Thank you for sharing this formula. When I try to export a list of FTB from Analytics using the Jump To dashboard function, it gives me a list of *all* constituents rather than just FTB. How can I work around this?
I'd be very interested to hear the solution to this as well - I have found that for my first timer widgets, plus tbh other widgets where the forumla is a bit more complex.
Hi Amy,
Because of the nature of the formula, the connection to the source constituents is lost and non-transferrable to the Jump To dashboard. The formula is in effect returning a hard-value to the widget and can't make out the constituents behind it.
To create a widget listing out the first timers: