6 Replies Latest reply on Oct 10, 2019 1:40 AM by Ciara Brennan

    Table Calculations

    Jim McHardy

      Hello.

       

      Below are the results of a table where I basically require a way to calculate the percentage of numbers that had a decrease between "Initial" and "Discharge".  I was hoping there is a table calculation, or series of calculations that could get me this result.  Only results with an "Initial" number > 0 should be counted (all highlighted in yellow is what I want....not the orange one).  My end result would be seeing that 5 out of the 6 yellow instances had a drop in number between "Initial" and "Discharge"  (5 / 6 = 83%) for that particular ID.   I would then require the percentage at the Program level as well for a total.

       

      EDIT:  .twbx sample file attached

       

      Any help would be greatly appreciated!

       

       

      ThanksPrePostSample.png

        • 1. Re: Table Calculations
          Will Blake

          Something like:

           

          {FIXED Program, ID, Initial >0:

          INT({FIXED Program, ID,3rd column, QUARTER(Date) : SUM(Initial)} - {FIXED Program, ID,3rd column, QUARTER(Date): SUM(Discharged) >0)/ INT({FIXED Program, ID,3rd column, QUARTER(Date) : COUNT(Initial)}

           

           

          And then add a Context filter to filter out the Initial ==0

           

          I Would need data to check it but it should be close

          • 2. Re: Table Calculations
            Jim McHardy

            Hi Will.

             

            I guess I should have clarified that the 3rd Column doesn't have a variable.  Those are all independent measures so they don't belong to any 1 Dimension or Measure as a group.  That's why I was hoping a table calculation could be useful.  I'll try to post some data when I get a chance to see if that helps.

             

            Also, "Initial" is the result of a Dimension, not a Dimension itself.  Sorry, I know it's not helpful to not have any data to play with.

             

            I appreciate where you're going with this.  I'll experiment with it a bit.

             

            Jim

            • 3. Re: Table Calculations
              Will Blake

              ah,

              I'd need to see how your data was set up but im guessing window calculations could help

              • 4. Re: Table Calculations
                Jim McHardy

                Okay I've attached some sample data.

                 

                As per my original posting, I'm looking to get the percentage that decreased from the Survey Type of initial to discharge.  In the below example ID 30112 has 4 of 6 that decreased, or 66%.  The one that went from 0 to 3 should be excluded.  Where survery = initial and the total is zero, it's not included in the calculation.  A window calculation may do the trick??

                 

                 

                PrePostSample2.png

                • 5. Re: Table Calculations
                  Will Blake

                  Ahh ok yeah I had a look but I think the best way might be to try and reshape your data. It might work if you can convert your match fields to a column otherwise you are stuck with the problem that you cant use measure values in calculations

                  • 6. Re: Table Calculations
                    Ciara Brennan

                    Hi Jim

                    If you have a couple of minutes could you please take this quick survey around your experience on the Tableau Community Forums?

                    ***Tell us about your experience on the Tableau Community Forums***

                     

                    Much appreciated
                    Ciara

                    [Program Manager | Tableau Community Forums]