-
1. Re: Need help on Calculation
Dimitri.B Oct 23, 2012 4:45 PM (in response to SILVIO MARTINEZ)1 of 1 people found this helpfulThis 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?
-
2. Re: Need help on Calculation
SILVIO MARTINEZ Oct 23, 2012 4:57 PM (in response to Dimitri.B)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
Dimitri.B Oct 23, 2012 5:01 PM (in response to SILVIO MARTINEZ)1 of 1 people found this helpfulI 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.
-
4. Re: Need help on Calculation
SILVIO MARTINEZ Oct 23, 2012 5:29 PM (in response to Dimitri.B)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
Dimitri.B Oct 23, 2012 6:02 PM (in response to SILVIO MARTINEZ)1 of 1 people found this helpfulThis 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.
-
6. Re: Need help on Calculation
SILVIO MARTINEZ Oct 24, 2012 10:50 AM (in response to Dimitri.B)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
Dimitri.B Oct 24, 2012 6:14 PM (in response to SILVIO MARTINEZ)1 of 1 people found this helpfulOne 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.
-
8. Re: Need help on Calculation
SILVIO MARTINEZ Oct 25, 2012 9:57 AM (in response to Dimitri.B)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
SILVIO MARTINEZ Oct 25, 2012 10:27 AM (in response to SILVIO MARTINEZ)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
Dimitri.B Oct 25, 2012 6:34 PM (in response to SILVIO MARTINEZ)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
http://community.tableau.com/thread/119212
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
Dimitri.B Oct 25, 2012 10:36 PM (in response to SILVIO MARTINEZ)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
SILVIO MARTINEZ Oct 26, 2012 1:05 PM (in response to Dimitri.B)Thx. What do you think about taking those Tableau Classroom courses?
-
13. Re: Need help on Calculation
Dimitri.B Oct 28, 2012 5:18 PM (in response to SILVIO MARTINEZ)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
SILVIO MARTINEZ Oct 28, 2012 5:42 PM (in response to Dimitri.B)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.