Hi Lovely People,
We have an education dashboard which has been really helpful as we are able to bucket the ticket types together to split them out into how our education team wants to report on them - currently this is by what is taught onsite, online & offsite (at the school).
We have set up performances that have Online or Outreach in the titles so I'm able to filter performances based on those names. For Onsite I basically have a formula that counts of all tickets less those tickets within the online & outreach performances. This worked well until this month where we've had a school book two online performances for the same class. What this has done is put the onsite ticket count into the negative as the formula is counting the tickets to each online performance.
This is the current formula
my 1st question - it there a way to have the count distinct? Or some way of making the formula only count a single performance from each order each time?
I've been trying other ideas out to see if I could find an alternative way to get to this result & landed on a case when statement as per below. I thought I had by making the performance filter look at online or outreach perfs only & if the ticket count is greater than 0, then make the count 0, else calculate the number of tickets as normal.
This works great as long as I have the school names included in the widget display but as soon as I'd finished my data checks, I removed the school name & all the onsite student counts went to zero - which looking at the formula, I understand why, but I can't work out how do I keep the number correct once I've moved to the consolidated view? I feel I've been staring at this for too long & am missing something very simple!
All ideas greatly welcomed!
Thanks
jo
Hey cool,
So I might be out of my depth but I think you are onto something.
The reason that the CASE WHEN drops out when you remove the School Names might be due to that field acting as a grouping element. ...
HENCE .... MULTI-PASS AGGREGATION.
So I'm grouping on Order ID (rather than Customer ID but you can do either)
and changing your CASE WHEN into a IF which kind of reads
SUM ( [Order ID] , ( IF ( ( [Total Ticket Count] , [Season] , [Performance] , [Price Type1] ) > 0 , 0 , ( [Total Ticket Count] , [Price Type] ) ) ) )
Hope this is ok ... took me a bit to find it in the .dash and I wish I had data to test. I added a bunch of spaces in the formulas to make it a little easier to read Also we talked about this at Analytic Coffee but adding it in the forum in case anyone has better ideas