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!
Hello all,
I have achieved this. This may not have been the most practical solution, but if someone asks for a similar dataset in the future, I will be prepared.
To achieve this, I used several formulas that were tapered versions of this:
case when [Min Season Fiscal Current Year Offset]= 0 then sum ( [Constituent ID],if (([Total Seat Count], [Season Fiscal Current Year Offset]) >0, 1, null))
when [Min Season Fiscal Current Year Offset]= -1 then sum ( [Constituent ID],if ( ([Total Seat Count], [Season Fiscal Current Year Offset]) >0 AND ([Total Seat Count], [Season Fiscal Current Year Offset1]) >0, 1, null))
when [Min Season Fiscal Current Year Offset]= -2 then Sum ([Constituent ID],if ( ([Total Seat Count], [Season Fiscal Current Year Offset]) >0 AND ([Total Seat Count], [Season Fiscal Current Year Offset1]) >0AND ([Total Seat Count], [Season Fiscal Current Year Offset2]) >0, 1, null)) when [Min Season Fiscal Current Year Offset]= -3 then Sum ([Constituent ID], if ( ([Total Seat Count], [Season Fiscal Current Year Offset]) >0 AND ([Total Seat Count], [Season Fiscal Current Year Offset1]) >0AND ([Total Seat Count], [Season Fiscal Current Year Offset2]) >0 AND ([Total Seat Count], [Season Fiscal Current Year Offset3]) >0, 1, null))
when [Min Season Fiscal Current Year Offset]= -4 then Sum ([Constituent ID], if ( ([Total Seat Count], [Season Fiscal Current Year Offset]) >0 AND ([Total Seat Count], [Season Fiscal Current Year Offset1]) >0AND ([Total Seat Count], [Season Fiscal Current Year Offset2]) >0 AND ([Total Seat Count], [Season Fiscal Current Year Offset3]) >0 AND ([Total Seat Count], [Season Fiscal Current Year Offset4]) >0, 1, null))
when [Min Season Fiscal Current Year Offset]= -5 then Sum ([Constituent ID], if ( ([Total Seat Count], [Season Fiscal Current Year Offset]) >0 AND ([Total Seat Count], [Season Fiscal Current Year Offset1]) >0AND ([Total Seat Count], [Season Fiscal Current Year Offset2]) >0 AND ([Total Seat Count], [Season Fiscal Current Year Offset3]) >0 AND ([Total Seat Count], [Season Fiscal Current Year Offset4]) >0 AND ([Total Seat Count], [Season Fiscal Current Year Offset5]) >0, 1, null))
when [Min Season Fiscal Current Year Offset]= -6 then Sum ([Constituent ID], if ( ([Total Seat Count], [Season Fiscal Current Year Offset]) >0 AND ([Total Seat Count], [Season Fiscal Current Year Offset1]) >0AND ([Total Seat Count], [Season Fiscal Current Year Offset2]) >0 AND ([Total Seat Count], [Season Fiscal Current Year Offset3]) >0 AND ([Total Seat Count], [Season Fiscal Current Year Offset4]) >0 AND ([Total Seat Count], [Season Fiscal Current Year Offset5]) >0 AND ([Total Seat Count], [Season Fiscal Current Year Offset6]) >0, 1, null))
when [Min Season Fiscal Current Year Offset]= -7 then Sum ([Constituent ID], if ( ([Total Seat Count], [Season Fiscal Current Year Offset]) >0 AND ([Total Seat Count], [Season Fiscal Current Year Offset1]) >0AND ([Total Seat Count], [Season Fiscal Current Year Offset2]) >0 AND ([Total Seat Count], [Season Fiscal Current Year Offset3]) >0 AND ([Total Seat Count], [Season Fiscal Current Year Offset4]) >0 AND ([Total Seat Count], [Season Fiscal Current Year Offset5]) >0 AND ([Total Seat Count], [Season Fiscal Current Year Offset6]) >0 AND ([Total Seat Count], [Season Fiscal Current Year Offset7]) >0, 1, null))
when [Min Season Fiscal Current Year Offset]= -8 then Sum ([Constituent ID], if ( ([Total Seat Count], [Season Fiscal Current Year Offset]) >0 AND ([Total Seat Count], [Season Fiscal Current Year Offset1]) >0AND ([Total Seat Count], [Season Fiscal Current Year Offset2]) >0 AND ([Total Seat Count], [Season Fiscal Current Year Offset3]) >0 AND ([Total Seat Count], [Season Fiscal Current Year Offset4]) >0 AND ([Total Seat Count], [Season Fiscal Current Year Offset5]) >0 AND ([Total Seat Count], [Season Fiscal Current Year Offset6]) >0 AND ([Total Seat Count], [Season Fiscal Current Year Offset7]) >0 AND ([Total Seat Count], [Season Fiscal Current Year Offset8]) >0, 1, null))end
I have attached the dashboard in case anyone would like to use this for themselves or critique my methods.
HolidayPops.dash
I hope you enjoy. Thank you for reading along.
John, that is a great example of piecing together different analytics concepts and using them together. Thanks for sharing.