Hi Folks --
As we continue our efforts to enable our power-users to pull dynamic queries without needing to resort to SQL, we've run into an Analytics request that has me stumped.
So, the request is this: for a Tessitura Season, how many unique customers attended n number of unique production seasons. The output from the SQL query that answers this question is below, along with the SQL. The SiSense conundrum is can we use an aggregate as a row value in order to then aggregate values based on that formula-based metric?
Thanks!
DGomez
select u.season_desc ,u.num_unique_prod_seasons ,count(distinct u.customer_no) as num_unique_customersfrom( select s.description as season_desc ,o.customer_no ,count(distinct p.prod_season_no) as num_unique_prod_seasons from T_ORDER o with (nolock) join T_SUB_LINEITEM sli with (nolock) on o.order_no = sli.order_no join T_PERF p with (nolock) on sli.perf_no = p.perf_no join T_PROD_SEASON ps with (nolock) on p.prod_season_no = ps.prod_season_no join TR_SEASON s with (nolock) on ps.season = s.id join TR_PRICE_TYPE pt with (nolock) on sli.price_type = pt.id join TR_PRICE_TYPE_CATEGORY ptc with (nolock) on pt.price_type_category = ptc.id --select * from TR_PRICE_TYPE_CATEGORY where sli.sli_status in (2,3,12) and ps.season in (269) --select * from TR_SEASON where fyear = 2020 and ptc.id not in (1,4) and exists ( select 1 from T_LIST_CONTENTS lc1 where lc1.customer_no = o.customer_no ) group by s.description, o.customer_no) as ugroup by u.season_desc, u.num_unique_prod_seasonsorder by u.season_desc, u.num_unique_prod_seasons
Hi Daniel,
I think one of the following two approaches would be better than jumping into changing the data model behind the Tessitura Analytics Seats and Tickets cube. The second is the simpler approach, but knowing you, this combination of bucketing, multi-pass aggregation, and conditional statements may interest you more. This approach produces a Value per bucket, so we will have five formulas grouped by Season on Rows in the Pivot Table.
SUM( [Constituent ID] , IF ( [# of unique Production Season ID] = 1 , 1 , NULL ) )
Breaking this formula down... For each Constituent ID (this is the multi-pass aggregation where we're grouping our value on another field, as we might do to group Performance Budget Amount on Performance ID before SUMming it) determine their unique count of Production Season ID (if you don't have Production Season ID, you can just use Production Season). Then if for any given Constituent ID that sum equals one, then spit out a 1, otherwise spit out a NULL. Finally, sum all those 1s and NULLs to get a result. (If you want to see 0s instead of blanks in your output you can use 0 in place of NULL in that formula.)
Repeat the process for as many additional Values as you'd like. I did 5. The subtotals you see for 4 and 5 that don't have values within the corresponding seasons are valid counts of constituents who meet the Production Season count criteria for the Season Fiscal Year, even though they don't meet it for any given Season within that fiscal year.
An alternative to this approach, if it really needs to be a dimensional field rather than a formulated value, one could configure a TX_ANALYTICS_DIVISION_ELEMENT Custom Range field, for a specific Season, to count distinct prod_season_no from Ticket History. That combined with a new TR_ANALYTICS_RANGE_TYPE named for this purpose, and entries in TX_ANALYTICS_DIVISION_RANGE for the desired buckets of distinct production season counts would produce the desired results after the next Analytics data load.
Either of these approaches avoid having to alter the data model.
Chris Wallingford Product Owner Tessitura Network office: +1 888.643.5778 x553 chris.wallingford@tessituranetwork.com
Worked well for me, Chris! The IF() is a revelation -- this is a fascinating way of approaching this and it works well. Thank you!