Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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]))
Not entirely sure I understand your requirement... would you be able to elaborate with an example?
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 |
Have you tried this?
Median(Aggr(Sum([Shelter Nights]), [Client Id]))
Actually this
=Median(Aggr(Median([Shelter Nights]), [Client Id]))
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]))
Wait, 43.75 is not the right value? How are you getting the 10?
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 |
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]))
Thank you. The formula worked.