# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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
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
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.
New Contributor

## Re: Finding median while ignoring 0 values

Thanks! It worked.