Hi everyone, this sounds so flipping simple but I can't figure it out. I feel like I'm not understanding some basic underlying logic behind lists/analytics/output sets.
I want to analyze the dollar value of patrons based on how many consecutive years they've been purchasing tickets. I figured the first step was to create lists of patrons who have purchased tickets every year the past 5 years, 4 years, 3 years, and so on, then break those lists down in Analytics.
So in my criteria, I select Ticket History Season and figured I could just select all five of the past years, and it would pull anyone who has ticket history in ALL of the selected seasons, but it's not working as I expected. I tried using "In" and "Has" (what's the difference??), I tried using five Ticket History Season criterion for each years, but I'm getting nothing.
What am I missing?? Thanks!
Hazel,
So this is one of those things that seems more complex than it should be because of how database languages work (though that is hard to tell through the front end editor). When List Manager looks through things, what it is doing is looking through the individual lines of a person's ticket history, looking for some line that has a data point or multiple data points to match to the criteria you have selected. But it looks that way, by each individual line; not comparing one line to another line to see how they differ.
So when you select all 5 seasons in the first place, what it is doing there is finding anyone who has ticket history in ANY of the past five seasons. So any one individual season, multiple or all five would all be returned. Not what you want. When you try doing 5 individual criteria, you probably get no results. That is because the database is trying to find someone who has ticket history in all 5 of those seasons in the exact same ticket history line. That would mean that one of their ticket history performances would have had to be simultaneously in all 5 of those seasons; which is not possible.
The only good way to accomplish this in my mind without using either a hand-written SQL command or else some other form of custom attribute is to create what seems like a lot of lists. Create a list for the first season. Then, for the second season, create a list that has that season AND a criteria for list IN that first list you created. And so forth so that the fifth list is the fifth season and a criteria for anyone who is in the fourth list. Cumbersome, for sure. But it gets the job done.
John
John, thank you for the explanation!
How come I don't ALWAYS get zero results if one Ticket History Season criterion has two season values selected? I haven't seen the back end, but I assume the season associated with an individual line in someone's ticket history is a foreign key referencing a season table... to be able to reference multiple seasons would imply that a single row can have an arbitrary number of foreign keys... though I may be fundamentally misunderstanding something.
What you are describing there is what happens when you have two separate Ticket History Season criterions. That does return zero because, yes, you cannot be both seasons.
When you have one Ticket History Season criterion with 2 (or more) selected, what is really happening is an or statement. So, it looks at the table to see if it has ID 1 or 2. If it has either, that patron is returned. So if you have 5 values, it would return anyone with 1, 2, 3, 4 or 5. And yes, that column IS an Integer column, so yes, the foreign key reference can only reference the ID from the TR_SEASON table.
Hopefully this clears things up even further!
Thank you!! That does clear things up and it behaved as you described!
Interestingly, it behaves as I expected if I use HAS instead of IS!
Ticket History Season HAS 2019Ticket History Season HAS 2018
Returns patrons who have a history that fulfills both conditions :)
Maybe my fault for forgetting that. I tend not to use "HAS" as often because A) I will just write a query myself and B) those tend to take longer to generate. But that is just me, I guess.
Yes, the "multiple same parameter with HAS" is the approved method for returning people who have more than one of the same type of record with different values. So you're asking for "everybody for whom a row exists in this table with this first value AND for whom a row exists in the same table with this second, different value."
Sadly I haven't been entrusted with the ability to query our database yet, so I guess it's HAS for me thanks again!