Hello,
I am trying to make a pivot table of customers who have bought tickets to productions in multiple years. The end result would look something like this:
In this table we have:
- 1118 constituents bought tickets in 2023
- 724 of those also had tickets in 2022
- 505 of those also had tickets in 2021
- 317 of those also had tickets in 2020
and so on...
After much work, I was able to generate one row of this, but it needed a formula for each column and did not work for subsequent rows. My formula is 4 variants of thiis
if ( ([Total Seat Count], [Season Fiscal Current Year Offset]) >0 AND ([Total Seat Count], [Season Fiscal Current Year Offset1]) >0 AND ([Total Seat Count], [Season Fiscal Current Year Offset2]) >0 AND ([Total Seat Count], [Season Fiscal Current Year Offset3]) >0, 1, null)
Is there an easier way to achieve this result?
Thank you!
I'm sure there are very clever formulas out there to achieve what you're looking for, but my instinct would be to make lists of returning ticket buyers and filter the values by those lists. Not a very fun answer, I know!