10 Replies Latest reply on Jun 13, 2016 7:23 AM by Mark Fraser

    Percentage of total

    Paula Fermin Cueto

      Hi all, I need help. Many times I have tried to get a graph like the one I have attached. I have a field with true/false and I would like to display in a graph line, for each day, the percentage of "true" records. In the picture I have attached I have two lines (one for true and one for false), that I have created dragging the true/false field to color and doing a quick table calcuation with percentage of total computing using table down. The problem is that I'm only interested in the percentage for the "true". I don't want to see both. Any ideas?

      Many thanls in advance

        • 1. Re: Percentage of total
          Mark Fraser

          Hi Paula

           

          Fun problem, and a few ways to solve...

          Easy way IF statement.

           

          TRUE COUNT = IF [your field] = 'TRUE' THEN COUNT([something])          (you count the activity for true only)

          FALSE COUNT = IF [your field] = 'FALSE' THEN COUNT([something])       (as above but for false)

          TOTAL = {COUNT([something])} << needs v9                                           (the total, for % calcs)

           

          % = SUM([TRUE COUNT])/ TOTAL

           

          If you had some fake example data, I can try and help further...

           

          Cheers

          Mark

          1 of 1 people found this helpful
          • 2. Re: Percentage of total
            Paula Fermin Cueto

            Hi Mark,


            Thank you very much for your quick response! Very much appreciated.

            I'm afraid I'm a quite a beginner. I don't understand where the code goes. Are those calculated fields? Fields editted in shelf?

            I have attached a sample file with some data. I just want to see, in a line graph the % of flights that were punctual

             

            Many thanks,

             

            Paula

            1 of 1 people found this helpful
            • 3. Re: Percentage of total
              Mark Fraser

              Hi Paula

               

              Thanks for the sample, we'll do it togehter, slow as you want

              First, save the Excel file, open Tableau, left hand side, click Excel, find the file, double click or click open.

              You should be here -

              The highlighted bit

              Column 1 is a date, Tableau has recognized that

              2, is a number, count of flights

              3, is a string, text.

               

              All is well, we can click sheet 1

               

              With me?

              1 of 1 people found this helpful
              • 4. Re: Percentage of total
                Mark Fraser

                I'll assume you are

                Next we need to make some calculated fields.

                 

                Hopefully you see this -

                Click the arrow, top Create Calculated Field

                Give it a name Delay Count, then enter this

                 

                IF [Delayed Flag] = 'Delayed' THEN [Flights]

                ELSE NULL END

                What does that do?

                If Delayed flag = Delayed, it returns the number of flights.

                If it doesn't equal Delayed, it returns nothing/null.

                 

                Exactly the same again, but for punctual.

                New calculated field, name = Punctual Count, then enter this

                 

                IF [Delayed Flag] = 'Punctual' THEN [Flights]

                ELSE NULL END

                 

                Stop.

                1 of 1 people found this helpful
                • 5. Re: Percentage of total
                  Mark Fraser

                  Hopefully you're still with me

                  One more calculated field - TOTAL

                   

                  Copy/Paste the below exactly

                  {INCLUDE [Scheduled Date] : SUM([Flights])}

                   

                  The {} need v9, they use level of detail

                  What it does? It says for every day, total the number of flights.

                   

                  You should now have 3 fields

                  Delay Count, Punctual Count and Total.

                   

                  Now its a good idea to check we are OK... I have made this view in Tableau and compared with the original

                  vs

                  All is well

                  1 of 1 people found this helpful
                  • 6. Re: Percentage of total
                    Paula Fermin Cueto

                    Sorry, I'm catching up now.

                    • 7. Re: Percentage of total
                      Mark Fraser

                      Now we know that's all working... we need to calculate the % of delay per day.

                       

                      Last calculated field - Delay %

                      SUM([Delay Count])/SUM([TOTAL])

                       

                      We now have the building blocks

                      Here I have created the view

                      completed v9.3.2 attached.

                       

                      One thing - the % from your original

                      These are them calculated in Excel

                      vs

                      Tableau matches Excel, but that doesn't match the original.

                       

                      Hope that helps/ is clear, any questions please come back to me

                       

                      Cheers

                      Mark

                      • 8. Re: Percentage of total
                        Paula Fermin Cueto

                        That is amazing!! Thank you very much.


                        Can I ask one last favour?

                        What if the flights were not aggregated for punctual and delayed? What if I had a list with a row for each file, like in the file I have attached. How would the process change?

                         

                         

                        Many thanks

                        • 9. Re: Percentage of total
                          Mark Fraser

                          Paula Fermin Cueto wrote:

                           

                          Sorry, I'm catching up now.

                           

                          Sorry Paula, I raced ahead

                           

                          I'm leaving shortly, so I wanted to get something written before i go, i can of course pick up questions if you have them

                          Basically, make the formulas (in italics) and then copy the layout i provided (i.e. date on the column, Delay % on row etc.)

                           

                           

                          If you want to show off... you can create a parameter so end users can swap between delayed and punctual.

                          To do this... New calculated field Activity %

                           

                          IF [Type] = 'Delayed' then [Delay %]

                          ELSEIF [Type] = 'Punctual' THEN [Punctual %]

                          END

                           

                          New parameter

                          Settings

                          Then swap Delay % for Activity %.

                          Right click the parameter (bottom left corner) - show

                          Sheet 2 attached

                          • 10. Re: Percentage of total
                            Mark Fraser

                            That is amazing!! Thank you very much.


                            Can I ask one last favour?

                            What if the flights were not aggregated for punctual and delayed? What if I had a list with a row for each file, like in the file I have attached. How would the process change?

                             

                             

                            Many thanks

                            Your very welcome.

                             

                            If they aren't aggregated - rather than return [Flights] you'd just return 1.

                            So

                            IF [Delayed Flag] = 'Delayed' THEN 1

                            ELSE NULL END

                             

                            IF [Delayed Flag] = 'Punctual' THEN 1

                            ELSE NULL END

                             

                            And total swaps from SUM to countd (d for distinct)

                            {INCLUDE [Scheduled Date] : COUNTD([Flights])}

                             

                            Should be fine