Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello there, I have an issue :). I have a pivot table with monthname(date) as dimension and country as the second dimension, like below:
for this I have used this expression:
(if(ColumnNo() >= 3 and ColumnNo()<=6,
after(Count({$<mga_LOST_STATUS={'Lost'}, mga_TRANSACTION_SUBSCRIPTION_MONTHS = {'12'}>} distinct mga_TRANSACTION_SUBSCRIPTION_ID),11)
/
COUNT({$<TABLE_KPI = {'Monthly Recurring Revenue (MRR)'}, mga_STATUS={'New'}, TABLE_VALUE-={0}, mga_TRANSACTION_SUBSCRIPTION_MONTHS = {'12'}>} distinct mga_TRANSACTION_SUBSCRIPTION_ID),
if(ColumnNo() > 6, 0.624)
) ) + sum({1} 0)
What I want is to replace the "if(ColumnNo() > 6, 0.624" with the median of the last 3 months, in this case the median of column 4,5,6 of this expression
after(Count({$<mga_LOST_STATUS={'Lost'}, mga_TRANSACTION_SUBSCRIPTION_MONTHS = {'12'}>} distinct mga_TRANSACTION_SUBSCRIPTION_ID),11)
/
COUNT({$<TABLE_KPI = {'Monthly Recurring Revenue (MRR)'}, mga_STATUS={'New'}, TABLE_VALUE-={0}, mga_TRANSACTION_SUBSCRIPTION_MONTHS = {'12'}>} distinct mga_TRANSACTION_SUBSCRIPTION_ID)
I tried with median(aggr(above expression),monthname(date),country) and all this in the if condition, but with no success. Any help would be appreciated
Any help?