• Replies 3 replies
  • Subscribers 962 subscribers
  • Views 154 views
  • Users 0 members are here
Related

Ticket buyers who have bought in multiple years pivot table

John Gerteisen
$organization

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:

  2023 2022 2021 2020
2023 1118 724 505 317
2022   2220 1005 670
2021     1395 1005
2020       1456

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!

  • $organization

    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!

  • $organization

    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. 

  • $organization in reply to John Gerteisen

    John, that is a great example of piecing together different analytics concepts and using them together. Thanks for sharing.