4 Replies Latest reply on Aug 20, 2015 11:33 PM by panjala Srinath

    How can i calculate % difference between 2 columns

    mehul parekh
    mehul parekh Aug 17, 2015 8:09 AM

      Hello ,

      I need help understanding how to write a formaula that can calculate % can and % completed in Tableau like it below that I did in excel.

      % cancelled and % completed are derived using formula(b2/d2)

      Thank you in advance

      Meh

      Count Based on Months QTY Rejected QTY CMp TOTAL QTY % CANCELLED %COMPLETED
      JAN-15 852 546 1398 60.94% 39.06%
      FEB-15 722 580 1302 55.45% 44.55%
      MARCH-15 765 650 1415 54.06% 45.94%
      APRIL-15 714 651 1365 52.31% 47.69%
      MAY-15 681 719 1400 48.64% 51.36%
      JUNE-15 741 860 1601 46.28% 53.72%
      JULY-15 834 920 1754 47.55% 52.45%
      TOTAL QTY 5309 4926 10235 51.87% 48.13%
        • 1. Re: How can i calculate % difference between 2 columns
          Vladislav Grigorov
          Vladislav Grigorov Aug 20, 2015 7:32 AM (in response to mehul parekh)

          Hello Mehul,

          Although Tableau very much resembles Excel's pivot tables, it is much different than excel in that Tableau is not cell-based, i.e. your formulas should be referencing fields or viz partitions instead of cells. Having said that, if your fields in Tableau match the column headings from above, the formulas (called in Tableau "calculated fields") should be like this:

          SUM([QTY Rejected]) / SUM([TOTAL QTY]) for your [% CANCELLED] field, and

          SUM([QTY CMp]) / SUM([TOTAL QTY]) for your [%COMPLETED] field.


          This assumes your data comes at the granularity presented above. Feel free to post a mockup workbook and/or some sample data if you need further help on this, so that I can have a look.


          Best,


          Vlad

          • 2. Re: How can i calculate % difference between 2 columns
            mehul parekh
            mehul parekh Aug 20, 2015 10:33 AM (in response to Vladislav Grigorov)

            Thanks for your response .

            actually it was my mistake in posting the question, QTY CMP and QTY REJECT are part of the main table QTY RESULT , so when I bring "QTY RESULT" in column shelf it created two columns QTY CANCEL and QTY COMPL and total quantity is by going to analsysis and checking total column .

            So where I am confused is how do I say that Cancel/grand total =% cancel because cancel is not a independent field it is the outcome from column "QTY RESULT: what I pasted is excel.and total qty wa calculated using b2+c2

            can u please guide me how to fix this ?

            • 3. Re: How can i calculate % difference between 2 columns
              Vladislav Grigorov
              Vladislav Grigorov Aug 20, 2015 11:14 PM (in response to mehul parekh)

              Hello Mehul,

               

              Have a look at this workbook that is a mockup of your situation based off superstore data. I tried to propose a solution based on my understanding of your data structure. If I guessed correctly, then you can do with simple aggregate measures as shown in the second sheet. However your data structure might be different, and you may need to resort to table calculations then. Feel free to post a packaged workbook and/or some sample data, so that I can hep you further with this.

              You can also have a look at this workbook, which treats quality inspections for discrete manufacturing process, where the outcome is either pass, or rework, or scrap. It applies basically the same technique - counting the records of each type for a certain period of time, and presenting aggregate statistics by various dimensions.

               

              Hope this helps,

               

              Vlad

              • 4. Re: How can i calculate % difference between 2 columns
                panjala Srinath
                panjala Srinath Aug 20, 2015 11:33 PM (in response to mehul parekh)

                Hi mehul parekh

                 

                create a calculated field

                 

                cancelled % = SUM([QTY Rejected]) / SUM([TOTAL QTY])

                Completed %=SUM([QTY CMp]) / SUM([TOTAL QTY])


                then after successful creation of calculated fields.

                1.right click on "cancelled %" field

                2.click on default properties

                3.choose number format

                4.then select percentage , Click ok

                 

                 

                repeat this for your other field Completed %

                Thanks & Regards

                Srinath. Panjala