I'm hoping to modify a widget that is already built using both the Seats and Tickets and Finance cubes.
Currently, the widget displays total revenue per performance for all Productions containing "Ceramics":
My end goal is to group all production seasons containing "All Levels Wheel" and "20a" which (there are multiple production seasons that fall into this category) and then see the total revenue for this set, as well as total number of students for this set. Each All Levels Wheel production season is a package containing multiple-day classes, and we have about 50 groups of packages of which All Levels wheel is an example. "A" stands for our spring Session, and all production seasons within our Spring Season contain 20A. 2020-20A971 is an example of one production season in our Spring 2020 lineup.
Eventually I will need to report on each Production Season broken down by Spring, Summer, and Fall for 2020 and 2021. All Production Seasons in Summer 2020 contain 20B, and for Fall contain 20C. I have about 50 class groupings and then 6 sessions for the two years 2020-2021.
Currently the only way I can figure out how to do this is create multiple Values in a Bar Chart, but the problem with this is eventually.I will need to create ~300 such value with the formula:
(sum([Ticket Paid Amount]),[Production Season])
The problem with this is eventually I'll have to create 300 such formulas: three for each of our 50 groups of production seasons, one for each spring, summer, and fall season, for both 2021 and 2022.
Here is what I have for 5 such instances:
I would guess there's an easier way to group all of the classes without having to create 300 values in various widgets, whether that's through inserting a case statement somewhere or whatever it may be.
Any insight anyone can provide would be much appreciated!
Hi Nicolaus,
Do you happen to have Production or even Title as an available method of grouping production seasons as being All Level Wheel vs Beginning Wheel? Alternatively, is the 20AXXX code also a part of the Event Code on the event? If so, does it need to be duplicated on the Performance name? I'm looking for ways to get that class name, by itself, in a field such that it could be grouped within the time of year.
For time of year, does it have to be Spring, Summer, Fall or can it be Q1, Q2...? The event dates within seasonal periods may not also line up with financial periods, but worth checking.
Ideally, I'd like to see you be able to us the same value fields across groups of something like a class Production field inside of an event date quarter.
And one other thought is the event Type field if that's not allocated for other reporting needs.
All Studio classes fall under the "Studio Tuition Classes" title. Then the Productions categorize each class type by age:
Then for Production Seasons we have the 22Axxx code, then in performance we have the 22Axxxzz code for each individual class in the package (1 Production Season = 1 package for our organization). The 20AXXX code is in fact also a part of the Event Code on the event.
The time of year doesn't always coincide with one fiscal year quarter, for example our Fall classes start in early September and end in December. And a customer could buy a Fall class in the Summer, as early as June which would be Q2.
20AXXX is also apart of the event code on each of the 5-15 events for each production season. Spring session runs in Q1 and Q2, and summer session starts early June ends mid-August, and then Fall is September-November/December.
All Studio Classes have the same Type of "Studio Classes"
Thanks for your feedback!