Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jbakerstull
Creator
Creator

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
sunny_talwar

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

View solution in original post

9 Replies
sunny_talwar

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

jbakerstull
Creator
Creator
Author

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
sunny_talwar

Have you tried this?

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

sunny_talwar

Actually this

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

Capture.PNG

jbakerstull
Creator
Creator
Author

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

sunny_talwar

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

jbakerstull
Creator
Creator
Author

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
sunny_talwar

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
Creator
Creator
Author

Thank you. The formula worked.