4 Replies Latest reply on Oct 9, 2013 6:25 AM by juliette.mccormack

    Charting Changes in Scores

    juliette.mccormack

      Hi All.

       

      Attached is a workbook showing an extract of some psychological therapies data.  I am trying to show for each completed referral whether a person's GAD7 Score has increased or reduced.  A reduction in the score is a good thing.

       

      The crosstab shows the correct results (I think!).  For each completed referral it takes the first score for the person and the last score.  This is not necessarily the first and last appointment as scores are not taken every time.

      Diff in GAD7SCORE chart (crosstab).jpg

       

      The LPTID is the person's ID, the SERVICEID is the referral ID.  If the ENDCODE has a code it is a completed referral so therefore all records without a code have been excluded.

       

      The trouble is that when I try to plot the results of this crosstab , I can't get it to show the correct figures.  I am trying to show the last column in the crosstab.  You will see that there is only one difference in score of +10.  However, my chart shows that there are 2 instances of +10.

      Diff in GAD7SCORE chart (chart).jpg

      I've only been using Tableau (v8) for a couple of months so please let me know if I've overcomplicated things, gone about it in the wrong way, etc.

       

      There are quite a number of scores collected so once I've mastered this one I would like to replicate it for the others.

       

      Many, many thanks for your help.

       

      Juliette

        • 1. Re: Charting Changes in Scores
          kevin.lynch

          Hi Juliette,

           

          I had a look through your workbook, the issue seems to be that you don't have Appointment_LPTID_1 on the Detail shelf for the Chart sheet.

           

          Not having this means is causing issues with the table calc's that you have set up.

           

          The entries that are getting bundled seem to be Appointment_SERVICEID 6 and 12704, these both have an Appointment_LPTID_1 value of 6.

           

          Let me know if this solves the issue for you...

           

          Also, kudos for doing such cool stuff with Tableau after only a few months!

           

          Thanks,
          Kevin

          • 2. Re: Charting Changes in Scores
            juliette.mccormack

            Hi Kevin

             

            Thank you v much.  That's done the trick.  Although I don't really understand why!  Showing my lack of experience.

             

            SERVICEID is a greater level of detail than LPTID so how can Tableau bundle 2 different SERVICEIDs together?  In my Crosstab sheet it seemingly makes no difference whether I have [Appointment_LPTID_1] on the Rows shelf or not because [Appointment_SERVICEID] is also there.  So why does it make the difference in the Chart?

             

            If you have time to help me understand that, it would be greatly appreciated.

             

            Thanks again.

             

            Juliette

            • 3. Re: Charting Changes in Scores
              kevin.lynch

              Hi Juliette,

               

              Glad I could help

               

              The issue arose due to the way you have the Index() function set up. If you edit the Index table calc, and under Compute Using select "Advanced" you can see that the original set up was like this:

               

              Table Calc 1.JPG.jpg

              Specifically, that you were partitioning the data on 'Day of Appt' and Indexing each 'Appointment_SERVICEID'.

               

              If you create a view with Day of Appt and then Appointment_SERVICEID on the Rows shelf, and add the Index function to the Text shelf, you can see more clearly what's happening.

               

              For your graph, the issue was that there were two Appointment_SERVICEIDs on 2009-10-07:

              Explanation Pane.JPG.jpg

               

              By adding Appointment_LPTID_1 to the detail shelf, the Index function is automatically updated to partition on it. So that there is only one entry for each occurrence (which is what you want because you are after a basic count).

               

              Let me know if you're still having trouble with it and I build a quick workbook out and send it on.


              Cheers,

              Kev

              1 of 1 people found this helpful
              • 4. Re: Charting Changes in Scores
                juliette.mccormack

                Hi Kevin

                Thanks v much.  That makes sense after following it through.  So much to remember!

                Juliette