# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Contributor

## RangeFractile (Medium) ALOS

I'm trying to understand how to use RangeFractile function to find the median average length of stay (per client).

Length of Stay per service Formula:

=If(substringcount([Exit Date],'.')=0,Floor(Num([Exit Date Adjusted])- IF([Entry Date]<'4/1/2018','4/1/2018',

Date(Floor([Entry Date]),'MM-DD-YYYY'))),Floor(Num('6/30/2018'-

IF([Entry Date]<'4/1/2018','4/1/2018',Date(Floor([Entry Date]),'MM-DD-YYYY')))))

Aver Length of stay (ALOS)

Sum(Aggr(If(substringcount([Exit Date],'.') = 0, Floor(Num([Exit Date Adjusted]) - [Entry Date]),

Floor(Num('6/30/2018' - [Entry Date]))), [Client Id], [Entry Exit Provider Name],

[Entry Date], [Exit Date Adjusted]))/Count([Client Id])

Apparently it's not as simple to just put RangeFractile in front of the sum.

Would I need to create a column in table that calculates ALOS for each client and then apply RangeFractile?

1 Solution

Accepted Solutions
MVP

## Re: RangeFractile (Medium) ALOS

okay, so may be you need this

=Median(Aggr(

If(SubStringCount([Exit Date], '.') = 0, Floor(Num([Exit Date Adjusted]) - [Entry Date]), Floor(Num('6/30/2018' - [Entry Date])))

, [Client Id], [Entry Exit Provider Name], [Entry Date], [Exit Date]))

9 Replies
MVP

## Re: RangeFractile (Medium) ALOS

Not entirely sure I understand your requirement... would you be able to elaborate with an example?

Contributor

## Re: RangeFractile (Medium) ALOS

Sorry, I meet median.

Goal is to figure come up with value of 43.75.

 Table Client Id Entry Exit Provider Name Entry Date Exit Date Shelter Nights 758 LSS - FM Faith on 6th 3/22/2018 4/5/2018 14 758 LSS - FM Faith on 6th 4/26/2018 6/4/2018 39 2134 LSS - FM Faith on 6th 3/30/2018 6/15/2018 77 2517 LSS - FM Faith on 6th 3/9/2018 5/9/2018 61 24362 LSS - FM Nancy's Place 4/2/2018 4/7/2018 5 24362 LSS - FM Nancy's Place 5/11/2018 5/14/2018 3 24362 LSS - FM Nancy's Place 5/24/2018 5/27/2018 3 24362 LSS - FM Nancy's Place 6/12/2018 6/14/2018 2

 Client Id Total Nights ALOS 758 53 26.5 2134 77 77 2517 61 61 24362 13 3.25 Median 43.75
MVP

## Re: RangeFractile (Medium) ALOS

Have you tried this?

Median(Aggr(Sum([Shelter Nights]), [Client Id]))

MVP

## Re: RangeFractile (Medium) ALOS

Actually this

=Median(Aggr(Median([Shelter Nights]), [Client Id]))

Contributor

## Re: RangeFractile (Medium) ALOS

Shelter Nights is based on a formula that calculates total nights per service. It's not a table value.

Formula is

=If(substringcount([Exit Date],'.')=0,Floor(Num([Exit Date Adjusted])-[Entry Date]),Floor(Num('6/30/2018'-[Entry Date])))

If I replace [Shelter Nights] with the formula the median value  returned is 43.75.  The expected value is 10.

=Median(Aggr(Median(If(substringcount([Exit Date],'.')=0,Floor(Num([Exit Date Adjusted])-[Entry Date]),Floor(Num('6/30/2018'-[Entry Date])))), [Client Id]))

MVP

## Re: RangeFractile (Medium) ALOS

Wait, 43.75 is not the right value? How are you getting the 10?

Contributor

## Re: RangeFractile (Medium) ALOS

I'm using excel, i used the median formula on shelter night column.

 Client Id Shelter Nights 758 14 758 39 2134 77 2517 61 24362 5 24362 3 24362 3 24362 2 Median 10
MVP

## Re: RangeFractile (Medium) ALOS

okay, so may be you need this

=Median(Aggr(

If(SubStringCount([Exit Date], '.') = 0, Floor(Num([Exit Date Adjusted]) - [Entry Date]), Floor(Num('6/30/2018' - [Entry Date])))

, [Client Id], [Entry Exit Provider Name], [Entry Date], [Exit Date]))

Contributor

## Re: RangeFractile (Medium) ALOS

Thank you. The formula worked.