Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
John_SM
Contributor III
Contributor III

Median projection in pivot table

Hello there, I have an issue :). I have a pivot table with monthname(date) as dimension and country as the second dimension, like below:

John_SM_0-1644512810395.png

 

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


 

1 Reply
John_SM
Contributor III
Contributor III
Author

Any help?