Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

yxchua
New Contributor

Finding median while ignoring 0 values

I'm trying to find the median of "Rental to sales" value by Name, ignoring the 0 values. 

However, I noticed the median function takes 0 values into consideration. How could I find the median value without the 0 values? Thanks.

 

My calculation for Rental to Sales:

(-1*
sum(aggr(
Sum({$<[PLLevel6]={'RENTAL'},Valid={'Y'}>}Amount),
[ID],[MonthYear])))
/
(sum(aggr(
if([Remote/Retail]='Remote'or [Category] = 'OCB',
Sum({$<[PLLevel5] = {'Sales'}>}Amount),

if([RetBranCategory]='Branch',
Sum({$<[PLLevel5] = {'Sales'},Valid={'Y'}>}Amount)+
Sum({$<[PLLevel5] = {'Sales'},Name={'TOTE'}>}Amount),
Sum({$<[PLLevel5] = {'Sales'},Valid={'Y'}>}Amount)
)),[MonthYear],[ID])))

 

Finding Median:

median(aggr((-1*
sum(aggr(
Sum({$<[PLLevel6]={'RENTAL'},Valid={'Y'}>}Amount),
[ID],[MonthYear])))
/
(sum(aggr(
if([Remote/Retail]='Remote'or [Category] = 'OCB',
Sum({$<[PLLevel5] = {'Sales'}>}Amount),

if([RetBranCategory]='Branch',
Sum({$<[PLLevel5] = {'Sales'},Valid={'Y'}>}Amount)+
Sum({$<[PLLevel5] = {'Sales'},Name={'TOTE'}>}Amount),
Sum({$<[PLLevel5] = {'Sales'},Valid={'Y'}>}Amount))),[MonthYear],[ID]))),[Name]))

1 Solution

Accepted Solutions
bethany_
New Contributor II

Re: Finding median while ignoring 0 values

Do you use the 0s elsewhere? You could change them in your load into a Null() rather than a 0 value so they are ignored by the formula.
2 Replies
bethany_
New Contributor II

Re: Finding median while ignoring 0 values

Do you use the 0s elsewhere? You could change them in your load into a Null() rather than a 0 value so they are ignored by the formula.
yxchua
New Contributor

Re: Finding median while ignoring 0 values

Thanks! It worked.