12 Replies Latest reply on Apr 20, 2018 10:55 AM by Amy Dias

# Skilled Nursing Facility Continuous Stay

Hi,

I am new to Tableau and am trying to determine a Skilled Nursing Facility (SNF) stay.  I have gotten as far as determining if the Stay is "New " or "Continuous" however I am at a point where I need to get the First "Claim From Date" and the last "Claim Thru Date" if the stay is labeled as "Continuous".  I can have multiple rows with "claim from date" and "claim thru date" for the same patient.  If a patient is in a SNF from 3/16/2017 thru 6/13/2017 the SNF will bill at the end of each month example:  Claim From: 3/16/2017 Claim Thru 3/31/2017, next claim for same patient would start 4/1/2017 thru 4/30/2017, then 5/1/2017 thru 5/31/2017 and continue on until the last date patient was admitted/discharged.  Therefore you you can have multiple rows with claim from date and claim thru date for the same patient.  I have a attached an example of what I have accomplished so far.  Any assistance would be much appreciated.

• ###### 1. Re: Skilled Nursing Facility Continuous Stay

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

• ###### 2. Re: Skilled Nursing Facility Continuous Stay

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

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/1-3/31

and 6/1-7/14). If you are interested in showing both of those lengths of stay

on one line, that will be trickier.

• ###### 4. Re: Skilled Nursing Facility Continuous Stay

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

Hi Amy (and swaroop.gantela)),

I've been watching this thread with interest as it touches on a use-case 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 non-unique) 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 pseudo-transaction 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!

• ###### 6. Re: Skilled Nursing Facility Continuous Stay

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

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 pseudo-transaction 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

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?

• ###### 9. Re: Skilled Nursing Facility Continuous Stay

Don,

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.

• ###### 10. Re: Skilled Nursing Facility Continuous Stay

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.

• ###### 12. Re: Skilled Nursing Facility Continuous Stay

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