
1. Re: Skilled Nursing Facility Continuous Stay
swaroop.gantela Apr 16, 2018 6:51 PM (in response to Amy Dias)Amy,
Welcome to the Forums.
I'm not sure that I fully caught the gist,
but in the workbook attached in the Forum Thread
is a method for getting the length of a continous stay.
The main goal is to get the First Date and Last Date
on the same row so that the difference in dates can
be calculated. (These steps can probably be condensed.)
[FirstFromDate]: If marked as continuous, then get the previous ClaimFromDate:
IF [Continued]="Continuous" AND LOOKUP([Continued],1)="New"
THEN LOOKUP(ATTR([Claim From Date]),1)
END
[LastThruDate]: Likewise, if this is the last Continuous, then get the ClaimThruDate:
IF [Continued]="Continuous" AND LAST()=0
OR [Continued]="Continuous" AND LOOKUP([Continued],1)="New"
THEN ATTR([Claim Thru Date])
END
[FirstDateCalc]: Spread the [FirstFromDate] across the all the continuous rows:
IF NOT(ISNULL([FirstFromDate]))
THEN [FirstFromDate]
ELSE PREVIOUS_VALUE([FirstFromDate])
END
Now the difference can be calculated:
IF NOT(ISNULL([LastThruDate]))
THEN DATEDIFF('day',[FirstDateCalc],[LastThruDate])
END

267611stay.twbx 32.5 KB


2. Re: Skilled Nursing Facility Continuous Stay
Amy Dias Apr 17, 2018 5:07 AM (in response to swaroop.gantela)Hi Swaroop
I can't thank you enough for looking at this for me, your calculations are close to what I need. I apologize if my first description was not clear/thorough enough.
The ultimate goal is to have just one complete row with beginning From Date to ending Thru date whether it is continuous or just a new stay. I still have repeating rows when I enter the new calculations. My ideal columns headers would be: Member ID  First Name  Last Name  DOB  First From Date  Last From Date  Difference, without any NULL values or NULL dates.

3. Re: Skilled Nursing Facility Continuous Stay
swaroop.gantela Apr 17, 2018 4:15 PM (in response to Amy Dias)Amy,
Apologies for missing that.
This still may not be quite what you need, but for the goal of getting the absolute first [claim from date]
and the absolute last [claim thru date] for every [memberID], you can use Level of Detail calculations to
fix those values to every row with that [memberID]:
First: { FIXED [Member ID]:MIN([Claim From Date])}
Last: { FIXED [Member ID]:MAX([Claim Thru Date])}
Since those get put on every row with that MemberID, you can get the datediff between.
This will give just one value for each Member ID. Please see sheet "Simplified" in the
workbook attached in the Forum Thread.
I apologize that I'm still a bit thrown by the New and Continuous part.
The first Member John Doe has two distinct continuous stays (3/13/31
and 6/17/14). If you are interested in showing both of those lengths of stay
on one line, that will be trickier.

267611stayB.twbx 76.8 KB


4. Re: Skilled Nursing Facility Continuous Stay
Amy Dias Apr 18, 2018 5:37 AM (in response to swaroop.gantela)Swaroop,
Thank you for your answer. Unfortunately it did not work for me simply because when doing my calculations I need to break them out yearly. As some of my patients have multiple stays in a SNF every year the fixed is giving me dates from their very first admission which could be from 2015 or 2016 when all I need is 2017. If I try to filter by year my current records that includes 2016 and 2017 my 2017 data is filtered out. My goal for this data is to be able to count all admissions. If a person has a "new" but it leads into a "continuous stay" then it is all one admission and needs to be counted once. If a person is a "new" without a "continuous" stay, then it is a single admission and should be counted once.
To further complicate the matter I also must be able to show if a person is admitted/discharged then readmitted within 30 days and show both the first admission dates and the second admissions dates. This can be done on a separate worksheet. I can take the datediff calculation to calculate if it is considered a readmission however I can not figure out how to show both claim from & claim thru dates on both the first admission and then the second admission that occurred within 30 days.
I hope there is an answer to this problem. I appreciate all the help you have given me to date.
My Best, Amy

5. Re: Skilled Nursing Facility Continuous Stay
Don Wise Apr 18, 2018 11:47 AM (in response to Amy Dias)Hi Amy (and swaroop.gantela)),
I've been watching this thread with interest as it touches on a usecase scenario that I might also have in the future.
Amy, is there any chance that your data has a transaction number associated with it? I would think so as there are billing dates involved? So, if the stay was new, it would have a unique billing or transaction number for that particular event period. Whereas if the stay were continuous, it would be a new transaction/billing number for the entire continuous event until they left?
In the newly attached workbook, I created a transaction number in your original set of dummy data (cleaned a few dates that appeared reversed and DOB's that appeared as sequential and nonunique) and was able to then achieve the ability to use a FIXED LOD for a new column of data called Single Event or Multiple Event which would then be easier to filter by for Swaroop.
I also created new DateDiff calc as I wasn't seeing how yours was working with all the negative values it was producing; in turn, I wasn't able to use my own DateDiff calc to then calculate the "New/Continuous" column. For some reason my DateDiff calc isn't producing a real integer, but is displaying correctly the number of days involved in between billing dates correctly, which I was hoping to use those positive integers to calculate the "New/Continuous" column. Because of this issue, I then brought in an Index() function and scoped it to the transaction level of detail per Member ID, and then used those integers to get to displaying correctly the "New/Continuous" column. I then created an LOD calc "Single/Multiple Event" {FIXED [Transaction #],[Member ID]: IIF (COUNT([Transaction #])=1, 'Single', 'Multiple')} to create a new column for filtering down to the appropriate date(s) you need, solely for multiple event situations. Perhaps Swaroop can use this?
The key here really is to have a lower level of detail or granularity if available, such as a unique billing or transaction number for the period of dates involved. If not, and I haven't seen this done yet, perhaps someone in the Tableau Community has some method of dynamically creating a pseudotransaction number based on whether the event period is single or continuous? Please see below screenshots and attached workbook as new example...not sure if it helps!

SNF(DW)_v10.4.twbx 31.7 KB


6. Re: Skilled Nursing Facility Continuous Stay
Amy Dias Apr 18, 2018 12:07 PM (in response to Don Wise)Hi Don,
Thank you for your response, unfortunately there is no unique transaction id. As the SNF bills they bill with a different Claim ID each time, even if the stay is continuous. I apologize, I'm uncertain why you could not see my DateDiff Calculation. Here is my version: DATEDIFF('day', LOOKUP(MIN([Claim Thru Date]), 1), MIN([Claim From Date])). I then computed the calculation using the deepest level and restarting at every Member ID. This seemed to give me the correct count without any negatives. I do appreciate you looking at the data.

7. Re: Skilled Nursing Facility Continuous Stay
Don Wise Apr 18, 2018 12:15 PM (in response to Amy Dias)Hi Amy,
That's unfortunate that they do that as that would've been helpful.
I see how this might be a dilemma as the only workable method of determining whether the stay was consecutive or not is solely through date calc's, which as you've seen with Swaroop's responses can be quite complicated. Or perhaps there's a method of creating that pseudotransaction number based on whether the dates are consecutive or not. I'l think this through a bit more as well. I know what you're trying to achieve, it's just the method of getting there.
Swaroop has done a great job so far.

8. Re: Skilled Nursing Facility Continuous Stay
Don Wise Apr 18, 2018 2:53 PM (in response to Amy Dias)Hi Amy & swaroop.gantela)),
Does perhaps the newly attached workbook help with the effort?
I think the additional column of identifying whether the dates involved are consecutive or not (grouping/filtering effort here) in combination with whether the event is new/continuous will help with bringing the date calc's into play?
Hopefully yes?

SNF_Stays(DW v.2)_v10.4.twbx 35.2 KB


9. Re: Skilled Nursing Facility Continuous Stay
swaroop.gantela Apr 18, 2018 8:17 PM (in response to Don Wise)Don,
Thank you, Sir! Your workbook is very helpful.
Grateful for your insight and clear, logical calculated fields.
Amy,
Not at all sure if this has gotten there, but I feel that it is inching closer.
In Table 2 of the attached, the last column should be showing the total number of days
if it is single, and if it is continuous only the total days in the last row of the claim.
This was done using Don's excellent flags of New/Continuous and Single/Consecutive.
The main idea was get the number of days in a claim month (one row) and then add in the previous value
if part of a continuous.
[ClaimDaysContinuous]:
// if the first of a continuous, return the row value of days
IF [New/Continuous]="New" AND [Single/Consecutive Event]="Consecutive" THEN SUM([ClaimDays])
// otherwise if continuous and consecutive, add to previous value
ELSEIF [New/Continuous]="Continuous" AND [Single/Consecutive Event]="Consecutive" THEN SUM([ClaimDays])+PREVIOUS_VALUE(0)
END
Then this uses conditionals to just get the single row value or the last row value:
[TotalClaimDays]:
// return the value for the last row for a member id if continuous
IF LAST()=0 AND [New/Continuous]="Continuous" THEN [ClaimDaysContinuous]
// return the value for the last of a continuous and the next one is new
ELSEIF [New/Continuous]="Continuous" AND LOOKUP([New/Continuous],1)="New" THEN [ClaimDaysContinuous]
// return the value for a single
ELSEIF [Single/Consecutive Event]="Single" THEN SUM([ClaimDays])
ELSE Null
END
These numbers can then be averaged or counted
//multiple copies are generated, so just get the first one
IF FIRST()=0
THEN WINDOW_AVG([TotalClaimDays])
END
OF NOTE: These methods are heavily dependent upon setting all the Compute Usings and Restartings correctly.
OF FURTHER NOTE: These methods may not adequately account for a continuous stay that starts in December and ends in January.

267611_SNFStays_v10.4.twbx 64.3 KB


10. Re: Skilled Nursing Facility Continuous Stay
swaroop.gantela Apr 19, 2018 8:12 AM (in response to swaroop.gantela)Amy,
We can collaborate on the 30 day readmits.
The plan will be to make a datediff calculation similar to what you had before.
If the New/Continuous flag is "New" and it's not the very first row for a MemberID,
then it is a readmission. So the time between admissions will be the difference
between the from date and the previous thru date.
If that is less than 30, then it can be flagged with a 1.
These 1s can be summed up for a year to get a total count of readmits < 30days.

11. Re: Skilled Nursing Facility Continuous Stay
Don Wise Apr 19, 2018 9:34 AM (in response to swaroop.gantela)Glad to have helped...!

12. Re: Skilled Nursing Facility Continuous Stay
Amy Dias Apr 20, 2018 10:55 AM (in response to swaroop.gantela)Hi Swaroop,
I definitely have the continuous stays the way I need to have them. Thank you for that. All the calculation that you and Don Wise helped me with have worked correctly and I am thinking thru how to get the 30 day readmits now. I will take your suggestion
"to make a datediff calculation similar to what you had before.
If the New/Continuous flag is "New" and it's not the very first row for a MemberID,
then it is a readmission. So the time between admissions will be the difference
between the from date and the previous thru date.
If that is less than 30, then it can be flagged with a 1.
These 1s can be summed up for a year to get a total count of readmits < 30days."
and see how far I can get with creating the correct calculation. Thank you so much for all your help to date!
Amy