Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

jbakerstull
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

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

Re: RangeFractile (Medium) ALOS

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

jbakerstull
Contributor

Re: RangeFractile (Medium) ALOS

Sorry, I meet median.

Goal is to figure come up with value of 43.75.

     

Table
Client IdEntry Exit Provider NameEntry DateExit DateShelter Nights
758LSS - FM Faith on 6th3/22/20184/5/201814
758LSS - FM Faith on 6th4/26/20186/4/201839
2134LSS - FM Faith on 6th3/30/20186/15/201877
2517LSS - FM Faith on 6th3/9/20185/9/201861
24362LSS - FM Nancy's Place4/2/20184/7/20185
24362LSS - FM Nancy's Place5/11/20185/14/20183
24362LSS - FM Nancy's Place5/24/20185/27/20183
24362LSS - FM Nancy's Place6/12/20186/14/20182

   

Client IdTotal NightsALOS
7585326.5
21347777
25176161
24362133.25
Median43.75

Re: RangeFractile (Medium) ALOS

Have you tried this?

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

Re: RangeFractile (Medium) ALOS

Actually this

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

Capture.PNG

jbakerstull
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]))

Re: RangeFractile (Medium) ALOS

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

jbakerstull
Contributor

Re: RangeFractile (Medium) ALOS

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

Client IdShelter Nights
75814
75839
213477
251761
243625
243623
243623
243622
Median10

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]))

jbakerstull
Contributor

Re: RangeFractile (Medium) ALOS

Thank you. The formula worked.

Community Browser