3 Replies Latest reply on Oct 24, 2017 1:36 PM by Manuel Velasquez

# Calculate length of stay over multiple dates

Good Day,

I'm hoping someone with advance knowledge of calculations can help me solve an ask from one of my program directors.

The ask was this - find the length of stay for all people that stayed with grant types = Federal RHY and HOPE.  The pickle for me is that Federal RHY and HOPE are individual cases and I'm not sure how to count the length of stay for only those two grant types.

In the attached workbook, there are two prime examples:

Ken Doll 14 stayed from 8/1/17 to 8/7/17 with grant type = Federal RHY.  That would count as a stay.

Ken Doll 14 also stayed from 8/15/17 to 9/19/17 with grant type = State BRS.  That would NOT count as a stay.

(the pickle) Ken Doll 14 also stayed from 9/20/17 to 9/21/17 with grant type Federal RHY, then stayed from 9/22/17 to 9/25/17 with grant type = HOPE (here 9/20/17 to 9/25/17 would count as a stay).

Ken Doll 86 stayed from 4/6/17 to 4/14/17 with grant type = State BRS.  That would NOT count as a stay.

Ken Doll 86 also stayed from 4/15/17 to 4/26/17 with grant type = Federal RHY.  That would count as a stay.

Thank you for taking a look.  I appreciate your time!!!

Paul

• ###### 1. Re: Calculate length of stay over multiple dates

Hi Paul

You could try an IF-THEN-END statement, to get the length of stay in "day"; something like this:

On your view, notice that when Grant is: "State BRS", there is no value (not counted)

Attached .twbx file for reference

Let me know if this helps

Manuel Velasquez

• ###### 2. Re: Calculate length of stay over multiple dates

Thanks Manuel,

This clearly demonstrates my wrinkle.  On Ken Doll 14.  You're totaling all Federal RHY and HOPE bednights.

My question from here is, is there a ways to break up the total to show and total for two stays.

1) The Federal RHY stay from 8/1 to 8/7 totals 6 days.  That's a stay.

2) The Federal RHY and HOPE stay from 9/20 to 9/25 totals 4.  That's a stay (because the stay is consecutive across Federal RHY and Hope grant types).

Paul

• ###### 3. Re: Calculate length of stay over multiple dates

Hi Paul

You could create a group with those 2 types of grant, however, because we are using both dates the granularity on the view is on that level.

Perhaps you find this article interesting Tableau Doctor: 1 Column, 2 Dates? Use Custom SQL | Tableau Software , it can give you more ideas on how your data can be modeled when working with dates.

Hope this helps!

Manuel Velasquez