Hi There! Thanks to these forums I have managed to put together a cracking new/return/lapse/churn dashboard - thank you!
I'm now looking to do a 5 year analysis with the following categories:
% attended every year for last 5 years
% attended only once in last 5 years
% attended more than once but less 5 of the last 5 years
I tried to do this in the same way I did the new/return/lapse widget. Because our Festival runs November - March I filter by Performance Detail Title. I thought I could do something like ([# of unique Constituent ID], [title 1] AND [title 2] AND [title 3]) etc where each title is the Festival year. This doesn't seem to work. Any suggestions on how to get % attended for last 5 years and % attended only once in last 5 years?
Thanks in advance!
Emily
Hi Emily,
For a Column or Bar widget filtered to include the last 5 years-worth of Festival runs, without anything on Categories, add 3 value formulas.
SUM ( [Constituent ID] , IF ( [# unique Season Fiscal Year] = 5 , 1 , 0 ) ) / [# unique Constituent ID]
SUM ( [Constituent ID] , IF ( [# unique Season Fiscal Year] = 1 , 1 , 0 ) ) / [# unique Constituent ID]
SUM ( [Constituent ID] , IF ( [# unique Season Fiscal Year] > 1 AND [# unique Season Fiscal Year] < 5, 1 , 0 ) ) / [# unique Constituent ID]
Each of these is returning the unique count of Season Fiscal Years in which each constituent held tickets, and then if they meet the criteria of having all 5 that are in the widget, or only 1, or something in between, then count them in the associated bucket.
Perfect! Thank you so much Chris Wallingford