14 Replies Latest reply on Jan 17, 2013 2:23 AM by Jonathan Drummey

    Using a calculated field

    Frank Hosea

      Using a calculated field I tried writing SUM ([Charge]) per ([MRN]) but the last part 'per ([MRN]) is problematic. Can someone show me how to write this?

        • 1. Re: Using a calculated field
          Shawn Wallwork

          Hmm. What does per() mean to you? I ask because it doesn't mean anything at all to Tableau (or me for that matter).

           

          --Shawn

          • 2. Re: Using a calculated field
            Alex Kerin

            Have you looked at the training videos - they are very good - here's one that pertains to writing calculations:

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

            • 3. Re: Using a calculated field
              Frank Hosea

              So let me ask the question how would you total individual charges by MRN without using the Analysis / Totals / Add Subtotals? I want to write a calculated field to return a specific range of MRNs basedon total amount and need a total row that is usable in such a field.

               

              The issue is if I use the standard feilds it will omit any charge per account number that is not in the defined range, thus lowering the actual total of the MRN.

               

              Each account is associted to a MRN so if someone has 10 visits with different charge per visit I want to add them all up and have a total for all the patients vists per MRN. Then I can use the filter to say give me every MRN total above 10,000 for example

               

              Frank 

              • 4. Re: Using a calculated field
                Frank Hosea

                I have looked at the On Demands which are indeed very good but I have not found anything that addresses this question specifically.

                • 5. Re: Using a calculated field
                  Alex Kerin

                  So in other words your actual question is radically different than "how do you divide one measure by another"?

                   

                  To answer your problem will need a tbwx workbook with the expected result described.

                  • 6. Re: Using a calculated field
                    Frank Hosea

                    The ' Analysis / Totals / Add Subtotals'  was indicating the path in Tableau not divisions symbols.

                    • 7. Re: Using a calculated field
                      Frank Hosea

                      This hospital just started using Tableau no more than a few months ago so I apologize if I am asking the wrong question. I can't send the workbook becuase it's patient data such as account number, medical record numbers, etc and we do no have test db to connect to, just live data.

                       

                      Let me ask the question as simply as I can as i may be convoluting things:

                       

                      What would be the best way to return say the top 50 patients (MRN numbers) who had the highets total charges?

                      • 8. Re: Using a calculated field
                        Shawn Wallwork

                        I'm with Alex that you really should post a sample workbook to help us better understand what you're trying to do. But having said that I'll take a shot in the dark and guess you may be looking for this table calculation:

                         

                        TOTAL(SUM([Charge])

                         

                        And then compute using MRN. If that's not it, we'll definitely need a sample workbook.

                         

                        --Shawn

                        • 9. Re: Using a calculated field
                          Frank Hosea

                          Can you tell me the compute steps using MRN? I got to the first past of this earlier but it is totaling all MRNs. That was why I added the per in my original stmt. The second part of this would solve my issue. 

                          • 10. Re: Using a calculated field
                            Shawn Wallwork

                            Now that's whole different kettle of fish. (See attached.)

                             

                            Frank.PNG

                             

                            In the future it's best to use the Super Store sample data set, and work out something close to your real world need. Then post a packaged workbook. Most everyone's data is confidential, so we try to use the super store whenever possible. If that doesn't work then scramble or dummy up your data and only post a couple of dozen rows, just enough for us to see what's going on.

                             

                            Frank Welcome to the forums!

                             

                            --Shawn

                            • 11. Re: Using a calculated field
                              Frank Hosea

                              Still not quite working. I've downloaded the workbook per your previous instructions. Could you send me directiosn ot the super store and how to upload? Thanks for your help tonight! - Frank

                              • 12. Re: Using a calculated field
                                Shawn Wallwork

                                The super store data set comes with all Tableau Desktop installations. When you first fire up Tableau it's on the first page:

                                 

                                Frank-2.PNG

                                 

                                --Shawn

                                • 13. Re: Using a calculated field
                                  Frank Hosea

                                  Great, thanks!

                                  • 14. Re: Using a calculated field
                                    Jonathan Drummey

                                    Hi Frank,

                                     

                                    Welcome to the forums! I'm pretty sure what Shawn sent should get you what you want.

                                     

                                    Here are a few extra pointers, I work for the quality management department in an integrated healthcare system and work with MRNs and accounts daily:

                                     

                                    - In the Superstore Sales data, think Customer for Patient/MRN and Order ID for Account/Visit. Also in the Superstore Sales, there's a third level of fine granularity, the Row ID that is associated with the Product purchased for a customer. You could translate that into a Diagnosis, Procedure, Meds, etc. (anything that there could be 1 or more of within a given Account aka Order ID). As an aside, I've been pushing for Tableau to have a Superstore Sales equivalent for healthcare, it would make a lot of things easier for us to learn and to share techniques.

                                     

                                    - If there are specifics in your data that are really germane to the question, then please take the time to mock-up some sample data or anonymize & randomize data that you have. Other than the Tableau staff who are on here, the rest of us who answer on the forums do so on our own time (and even some of the Tableau staff can be found on here answering at odd hours), so you can do us a favor by giving us the best picture of what you are working with. If you need some guidance, I put together a post on anonymizing and randomizing data at http://community.tableau.com/thread/118843.

                                     

                                    - One of the wonderful aspects of Tableau is that there are often several routes to achieving the same results. You started out this thread with "Using a calculated field I tried writing SUM ([Charge]) per ([MRN]) but the last part 'per ([MRN]) is problematic. Can someone show me how to write this?" What I've found as I've been using Tableau is that oftentimes I'm stuck because I've gotten myself into a dead end (like trying to do a filter on a Tableau subtotal column/row). Taking the time to explain my goal and the underlying data leads to faster/more answers, and sometimes I even figure an answer out myself as I'm writing up the question to post!

                                     

                                    - If you haven't yet, watch the free on-demand Tableau Training videos at http://www.tableausoftware.com/learn/training. If videos aren't your thing, I recommend Steve & Eileen McDaniel's book Rapid Graphs with Tableau. Or you can be a Tableau addict like me and do both. Both the videos and book will give you a solid grounding in Tableau, including filtering and sorting.

                                     

                                    - Also if you haven't yet, please join the Tableau Healthcare Users Group, at http://community.tableau.com/groups/healthcare.

                                     

                                    Cheers,

                                     

                                    Jonathan