2 Replies Latest reply on Jun 26, 2013 9:22 PM by di wu

    Build flexible calculation

    di wu

      Hi

         The issue we meet is a little complicate.I will start with a example.

      We have data like this.To simplify question.let us assume there are only 6 rows in the table.

       

      Product    Period         Count   Profit

          A             201301    null           20

          A             201301     30           null

          A             201302     30           null

          A             201302     null          20

      ------------------------------------------------------------

          B             201301    null           30

          B             201302    null           30

          B             201302     30           null

      .... 

       

           We need build a calculate field "Profit rate", which logic is:

      for each Product,computing               

                           Sum(Profit)/Sum(Count)

      So for Product = A, "Profit rate" in 201301 should be 20/30 = 80%

      "Profit rate" of 201301 and 201302 should be (20+20)/(30+30)

      That is just perfect situation.

           But in some month.Only one of the Profit or Count exist.Like product B,See the record marked in red,it only have Profit in 201301,

      Count is missing.So "Profit rate" in 201301 should be zero.in 201302 should be 30/30 = 100%,But when computing "Profit rate" for 201301 and  201302.The "Profit rate" should be (30 + 30) / 30 = 200 %.Here comes the question,When only one of Count and Profit exist we want to ignoring the records of this month when computing "Profit rate" in multiple Period.So The right "Profit rate" of 201301 and 201302 should be 30/30 = 100%.

            I really don't want to make that in database level.So I wonder if there is some solution exist in tableau.

      I have tried to count records for each product.and setting table calculation partition by Product.Then constrain the record count distinct equal to 2.

      But this table calculation need add more columns in level of detail than I need in report.Now I am really stuck by this issue.

             I hope I explain my issue clearly. by the way,I think tableau may need to enhance partition function.when I need to make some partition calculation.I need to put partition column on level of detail.That design is really not flexible in my opinion.Maybe it's not design for that,But it's really great to make it better.

              Look forward to everyone's advice.

        • 1. Re: Build flexible calculation
          Mark Holtz

          Di,

           

          This should be possible in Tableau. You are right that you will want to change the partitioning for table calculations of WINDOW_SUM(SUM([Profit]) and WINDOW_SUM(SUM([Count]).

           

          You can achieve this by creating a parameter and calculated field. Create a parameter as a string list with list options of "Period-Product", "Product" or "Period"

           

          Then, create the calculated field called [PartitionValue] with something like
          IF [parameter] = 'Product' THEN STR([Product])
          ELSEIF [parameter] = 'Period' THEN STR([Period])
          ELSEIF [parameter] = 'Period-Product' THEN STR([Period])+'-'+STR([Product])
          END

           

          Then, you can partition using the [calculated field] (which is dynamically selected by the user via the parameter) in your table calculation.

           

          If you have more questions, you'll need to share a packaged workbook illustrating your issue.

          • 2. Re: Build flexible calculation
            di wu

            Hi Mark

                Thanks for your advice.I learn new things from it.

            But for my case.I don't want to change partition using parameter.I need partition to see if the product profit and count is both exist in one period.Then decide if it's included or excluded in multi-period "Profit rate" computing.

             

            So in my thought.There shoud be a filter which enclose a countd()=2 formula.

             

            Thanks.

            Di