4 Replies Latest reply on Feb 6, 2012 10:14 PM by deepthi.mn

    Sort a field which is calculated using total() function

      Hi,

       

      I am trying to calculate a field using the following formula:

       

      Sales_contribution = (sum([Sales_Test_Post($)]) - sum([Sales_Test_Pre($)])) / (total(sum([Sales_Test_Pre($)]))) -

                                        (sum([Sales_Control_Post($)]) - sum([Sales_Control_Pre($)])) / (total(sum([Sales_Control_Pre($)])))

       

      Following are the problems I am facing with this:

      1. I cannot order/sort the table by Sales_contribution

      2. Cannot define Sales_contribution as global variable

       

      I figured out that the above two problems arise only when total() function is used.

       

      Please do let me know how to proceed with this.

       

      Thanks,

      Deepthi

        • 1. Re: Sort a field which is calculated using total() function
          Dimitri.B

          Use of TOTAL() makes it a table calculation, and as such its result will depend on how the data is partitioned (the Compute using thing that you set for table calculations). So it kind of doesn't make sense to sort on it.

          What do you mean by global variable?

          • 2. Re: Sort a field which is calculated using total() function

            Dimitri,

             

            I understand that TOTAL() is applied on partitioned data. But the field which I am calculating now is sales_contribution (See formula in my post above). Here i would want to sort the data based on contribution to understand which particular SKU is contributing highest to the sales. So it does make sense to sort it.

             

            By global variable I mean global filter which when applied will be applied on all dashboards.

             

            Let me know if can think of any work around.

             

            Regards,

            Deepthi

            • 3. Re: Sort a field which is calculated using total() function
              Dimitri.B

              By "doesn't make sense" I meant to say that it doesn't make sense from Tableau's perspective. To better re-phrase it: when sorting on a field or normal calculation, there is only one way to calculate values so Tableau can figure out how to sort, but with table calculations there are many ways for Tableau to calculate, depending on how table calc is parititoned, so it either has to ask the user to set partitioning (maybe this feature will be added in future?), or disallow sorting.

              I think the same reason prevents it from being a global filter.

               

              As a possible workaround, I can only think of reshaping the data in such a way that you don't need table calculations.

              If you post a workbook with sample data, maybe someone will find other ways to accomplish what you want.

              • 4. Re: Sort a field which is calculated using total() function

                Thanks Dimitri. That makes sense.

                 

                Actually adding this feature will be very helpful. For now i've achieved this by reshaping the data in the required format.

                 

                Regards,

                Deepthi