Sisense Pivot of a Pivot or Summarizing an Aggregate Pivot Table

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_customers
from
(
    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 u
group by u.season_desc, u.num_unique_prod_seasons
order by u.season_desc, u.num_unique_prod_seasons