Hi all,
I have found a Count Performances Past Year filter in the Seats and Tickets cube. While I'm sure this is great, I am looking for patrons with performances >1 since their first performance date, not just in the past year. I'm pretty new at Analytics, and don't know if I can edit the "past year" portion of this to be "since X date"?
I can get what I'm looking for in a pivot table, but can't seem to convert that to an Indicator widget.
(Does this even make sense??)
Happy to screen share etc, if that is helpful.
Hi Lesley,
The easiest thing I can recommend is a formula in Analytics. It's possible to modify Count Performance Past Year to be not limited to the past year and instead include all time. It's also possible to duplicate the logic of that Constituent Element into another Custom Element, and then set that duplicate to include all time. However, if you just need the number of constituents with >1 performance in their history, we can use Multi-Pass Aggregation in an indicator widget to get that.
SUM ( [Constituent ID] , IF ( COUNT ( [Performance ID] ) > 1 , 1 , 0 ) )
This says, if, for each Constituent ID, their total count of unique Performance ID is > 1, then add a 1 to the overall sum, otherwise add a 0 for that constituent.