2 Replies Latest reply on Sep 27, 2012 6:19 AM by Taha Gökçe

    "move to columns" and further calculations

    Taha Gökçe

      Hi,

      I have a data in my database in the form below:

      DATEGROUPTITLETYPE
      01.01.2012AMAST73A
      02.01.2012AMAST73A
      03.01.2012AMAST73B
      01.01.2012AACT73B
      02.01.2012AACT73B
      03.01.2012AACT73A

       

      I want to see the total percentage of change of the type from "MAST" to "ACT" for corresponding (pair) rows. To do this; I think I have to take my data into Access and shape the data into the form below: (just "move to columns"). Then I can create a calculated field and find the percentage.

      DATEGROUPMASTACT
      01.01.2012A73A73A
      02.01.2012A73A73B
      03.01.2012A73B73B

       

      I am asking now if there is a way in Tableau to acquire this result without using Access?

      I can visualize the second table above in Tableau, but I can't create a calculated field over that table because the data is originally in the first form, and how can it find the corresponding row in the first form to compare the equality?

        • 1. Re: "move to columns" and further calculations
          Tracy Rodgers

          Hi Taha,

           

          This can be done by using a table calculation, using a formula similar to the following:

           

          (ZN(SUM([Number of Records])) - LOOKUP(ZN(SUM([Number of Records])), -1)) / ABS(LOOKUP(ZN(SUM([Number of Records])), -1))

           

          Then, place this on the view. It will be necessary to edit how the table calculation is being computed--it will probably need an advanced compute.

           

          Hope this helps a bit!

           

          -Tracy

          • 2. Re: "move to columns" and further calculations
            Taha Gökçe

            Hi Tracy,
            I think lookup function looks up +/-n rows according to offset point. But I don't have a regular table to use a determined "n". The pair row is somewhere there in the data.
            I think it may only look up the pair row by using a "for loop", but is there any available "for loop"? If there is, will it be efficient to use here such a code like a macro?