14 Replies Latest reply on Oct 28, 2012 5:42 PM by SILVIO MARTINEZ

# Need help on Calculation

Can anyone help with the below?  I created a calculation for Length of Stay already (Discharge Date - Admission Date = LOS ) but I need it to add up the LOS Days.  The complexity is that there can be multiple admissions and discharges within the same period.

Below is an example.

A                             B                           C                         = C - B

NameAdmission DateDischarge DateLength of Stay (Days)
John Doe9-2-20129-5-20123
John Doe9-9-20129-15-20126
Mickey Mouse9-3-20129-10-20127
Mickey Mouse

9-17-2012

9-21-2012

4

George Jungle9-8-20129-18-201210
Winnie Poo9-19-20129-20-20121
Felix Cat9-5-20129-25-201220

Total Length of Stay in Septemeber is 51 days

I have created a string for Name +  Admission + Discharge and used it as COUNT (Distinct) to get total discharges for the month (7 separate discharges in Sept) but now I need a sum for Length of Stay (Days)

Can anyone help?  Attached a sample data and TDE & TWB.

• ###### 1. Re: Need help on Calculation

This should just work automatically, Tableau should sum the LOS, all you need is arrange your sheet the way you want. What exactly doesn't work with trying to sum LOS?

1 of 1 people found this helpful
• ###### 2. Re: Need help on Calculation

The issue is that there could be multiple records for each person which would increase the LOS sum calculation.

Currently using this formaula to count distinct:   str([MedRecNum]) + ' - ' + str([PtAdmitDate]) + ' - ' + str([PtDischDate])

The count is fine but can't the distinct LOS sum.  I think I need help on a sum distinct formula.  Check the previous entry for sample data.  Really odd.

• ###### 3. Re: Need help on Calculation

I briefly looked at your data and it looks like there are duplicate records from LOS point of view. Is that what is causing the problem?

I don't think there is SUM DISTINCT option.

Removing duplicates from data is one way to go, otherwise you'll need to find a way to suppress them or neutralise their effect. For example, if there is a recognisable pattern, i.e. if there are exactly two records for each stay, then halving the result will give the correct number.

1 of 1 people found this helpful
• ###### 4. Re: Need help on Calculation

These aren't really duplicates just separate occurences for the same person.

Example:

Silvio admits on 9-2-2012 and discharges on 9-8-2012 = Length of stay is  6 days

Silvio re-admits on  9-12-2012 and discharges on 9-22-2012 =  Length of stay is 10 days

This should give me 2 separate discharges for Sep-12 and a total Length of Stay of 16 days (6+10)

• ###### 5. Re: Need help on Calculation

This is a snapshot of your data for one customer - last three records apparently relate to the same admission-discharge event, thus tripling the LOS in your calculation.

1 of 1 people found this helpful
• ###### 6. Re: Need help on Calculation

The 3 highlighted customers are needed to to provide treatment count.  However, for Length of stay (LOS), I need to somehow come up with a formula that uses first admission date & the last admission date (Last Discharge Date-  First Admission Date) to give me a Length of stay (LOS) so that it can summed for a particular period.

Currently have a Treatment Count formula which strings and use Count Distinct:   = str([Medical #]) + ' - ' + str([Admission Date])+ ' - ' + str([Treatment Date])+ ' - ' + str([Discharge Date])

See the attached for explanation and data.   Thx in advance for your help.

• ###### 7. Re: Need help on Calculation

One way to do it involves a bit of acrobatics with table calculations (see attached workbook with a solution).

The idea is to partition the data (inside table calculations, not the actual data) in such a way that all treatments within one stay will represent a block, and then length of stay is calculated for each block and then LOS from all blocks are added for each patient. Same for number of treatments.

To accomplish this I needed a unique identifier for each patient, so I did the Full Name = Last Name & First Name, but there will be a problem if you have more than one John Smith in your real data. Unique person ID would solve that.

You can try changing data source for attached workbook to point to your real data, or re-creating calculated fields, (both formulae and partitioning) and sheet layout in your workbook. Explaining how it works in Tableau is not easy and requires solid knowledge of table calculations to understand. Let me know if you have any questions.

1 of 1 people found this helpful
• ###### 8. Re: Need help on Calculation

Thx Dimitri.  This helps.

As far as the unique person ID, there multiple patients that are in and out of clinics within a month.  Each occurrence needs to be measured for Length of Stay, Treatment count, and Re-Admission count,  Discharge count. Using patient names would probably not work as names can be very similar.

How can the below string help with the unique person ID?

str([Medical #]) + ' - ' + str([Admission Date])+ ' - ' + str([Treatment Date])+ ' - ' + str([Discharge Date])

• ###### 9. Re: Need help on Calculation

Where can I educate myself on solving items like this? The Tableau Tutorials seem very basic and don't really seem to apply much practicality for me.

• ###### 10. Re: Need help on Calculation

The subject of table calculations is a bit of a "rocket science". I have to admit that I often use trial and error to get table calcs to work, there seem to be a big element of "voodoo" in mastering them.

Anyway, here are some resources that can help, in order of increasing complexity:

http://www.tableausoftware.com/learn/tutorials/on-demand/table-calculations#tour

http://www.tableausoftware.com/sites/default/files/pages/table_calcs_in_tableau_6.pdf

http://www.tableausoftware.com/table-calculations

Unfortunately, there is no unified "mother of all tutorials", or a book, that would cover most, if not all, aspects of TC.

• ###### 11. Re: Need help on Calculation

SILVIO MARTINEZ wrote:

...

How can the below string help with the unique person ID?

str([Medical #]) + ' - ' + str([Admission Date])+ ' - ' + str([Treatment Date])+ ' - ' + str([Discharge Date])

It will not help, because this method will split one person into multiple versions of oneself - one for each treatment. The ID must be the same for all treatments and admissions, identifying the person.

• ###### 12. Re: Need help on Calculation

Thx.  What do you think about taking those Tableau Classroom courses?

• ###### 13. Re: Need help on Calculation

SILVIO MARTINEZ wrote:

Thx.  What do you think about taking those Tableau Classroom courses?

Much depends on who does the teaching, who else is in the class with you, whether you prefer instructor training or own pace, etc..

The one I attended had more practice at building the required vizes, and less of discussions and explanations of how things work. It is neither good nor bad, all depends on what you want to learn.

• ###### 14. Re: Need help on Calculation

Not sure who is teaching as I just signed for the fundamental and advanced for next week.  I want learn report building, calculations, dashboards, and best way to arrange my data.  Currently, we will have 1 master consolidated Excel file  which started being ~100 Excel templates from customers. Calculations for Length of stay, discharges, re-admission, DRGs,and other key metrics and filters.