Hello!
I'm hoping that this is a really easy fix and I'm just missing something obvious.
I'm trying to get an indicator widget to show the number of constituents who bought tickets for the 2018, 2019, 2021 OR 2022 seasons, with a ticket paid amount >= $200. I've started with a pivot table, and when I export it to CSV and take the constituent ID's and remove dupes, I get 1,071. When I remove columns and other values in the pivot table and just look for # of unique Constituent ID, I also get 1,071--so far, so good. Then, when I take the pivot table, duplicate it, and turn it into an indicator, it becomes 2,080 every time. I'm certain that it's counting the constituent for every season they bought tickets, but I'm not sure how I can get it to stop. I can get it to work in a similar widget for contributions over those same fiscal years, but not here.
I feel certain it's something glaringly obvious and I've probably even encountered it before. Let me know if you need me to share more information to help troubleshoot.
Thanks!
Michael Dorsey
Hey Michael. Want to give some MPA a go? In fact why not make it a double**?
So this is prefaced on the interperetation that you want count of people who spent more than $200 per order (rather than per ticket which is easier but different)
It'd be worth getting the thumbs up from CW bcause this is wacky and I won't don't have access to analytics outside the v16 sandbox for sometime. Good thing about the sandbox is if you upload my dashboard then we are working on the same data (hmmm maybe this should be a feature?), but note that the sandbox data is wierd*.
So what I think you are looking at is 2 multipath agregations
SUM ( [Constituent ID], IF ( SUM( [Order ID], IF ( [Total Ticket Paid Amount] > 200 , 1, NULL ) ) > 0 , 1 , NULL) )
The first multipass aggregation in blue goes a little like this. Grouping by Order ID - for order that buys tickets to Faust and spends more than $200 count them as 1. We have 22 of those orders
The 2nd MPA in green wraps around it and says for all these order id's (above $200 for Faust) count 1 everytime you see a new constituent. We have 14 of those people.
Test it out ... it seems to work but wrapping a multipass around another seems kooky. I've attached my sandbox dash in case you want to check it out. The indicator widget you should be able to drag and drop. I also took a shot using a single Prod Season in the board filter but it shouldn't change much filtering by multiple seasons in your case. I'm curious how it would go with mixed orders and wether it counts the whole order total or just the filtered products (I suspect the later).
Here is a diagram for a Multipass aggregation and there are plenty of other resources in the halp! menu if you need a refresher. The format is function(grouping field, function(numeric field))
*Faust is a 2019 show that runs till 2025, has performance that changes from single letters to description, with a timetable that would have it monitored by the FBI. To quote Dr Ray Stantz "Nobody ever made them like this! The architect was either a certified genius or an authentic wacko!"
** This Coffee reference is a cheap attempt at branding [editors]
SNDB-CountSpend.dash
Wow, this is amazing. Because of your input, I was able to get to my 1,071; however, I soon after realized that number was flawed because turning off the season row in the pivot table widget gave me the count of constituent ID when a constituents Total Ticket Paid amount was >= 200 across all 4 seasons. What I need is to show the number of constituents who paid $200 or more in at least one season out of the last 4 (2018, -19, -21, -22). Chris swooped in with that bit, and thanks to the two of you I feel confident in the numbers I'm getting back. I've learned a lot from this, and I'm very glad I decided to come to Analytics Coffee for help. Thank you for the double-shot of knowledge, Heath and Chris!