Active Customers for Max Quarter (Year-Q)

[d_prashanthredd]

Hi Experts,

I'm pulling data from a third party DB and which contains few dimensions, current quarter revenu and previous quarter revenue (grouped by a Year-Quarter).

My requirement is to build a dashboard with Active, Lost and New customers.

Note: My all calculations should be grouped by Quarter and Customer

Expressiosn used

Active: sum(if(aggr(sum(CurrentRevenue), Customer, YearQ)>0, 1))

Lost: sum(if(aggr(sum(CurrentRevenue), Customer, YearQ)<=0 and aggr(sum(PrevRevenue), Customer, YearQ)>0, 1))

New: sum(if(aggr(sum(CurrentRevenue), Customer, YearQ)>0 and aggr(sum(PrevRevenue), Customer, YearQ)<=0, 1))

Now,

I want to have one KPI which should display max quarter Active Customers and one more with New Customers. I tried by using Maxstring but failed to get it.