1 Reply Latest reply on Sep 26, 2016 5:00 AM by Yuriy Fal

    Subtracting min value to running calculation dynamically

    Anshul Gupta

      Hi All,

       

      I am stuck with dynamically calculation, data need to be calculated on the basis of yearly and quarterly calculation along with the components.

       

      Capture.PNG

      Above image is the given data which needs to convert to below calculations:

       

      Capture.PNG

      Data of 2013 Quarter 1 remains the same, calculation starts with 2013 Q2, the calculation is:

      {46 (2013 Q1 Value of same component) - 12 (Min value of 2013 Q1) } + 88 (Current Data's 2013 Q2 Value of same component)

      that will give 122, the same process follows with further calculation.

       

      I tried doing the calculation but not able to subtract from previous quarter's minimum value. I have attached the excel with contains all the formulae and my tableau workbook.

       

      Please help me out!!!.

       

      Thanks.

        • 1. Re: Subtracting min value to running calculation dynamically
          Yuriy Fal

          Hi Anshul,

           

          This is definitely the case

          where Excel (with it's Cell-based calculations) shines,

          whereas Tableau (with it's Set-based calcs) doesn't.

           

          There is a recursion here in the calcs,

          and the only recursive function in Tableau --

          PREVIOUS_VALUE() -- doesn't suffice.

           

          If it is an obligation to build such calculations in Tableau,

          one should be using a distinct set of (nested Table) Calculations

          for every new distinct Year-Quarter value (a column on a view).

           

          This is complicated, error-prone, and it doesn't scale at all.

          Anyway, please find the attached as an example of this brut-force approach.

           

          Hope it could help understanding what Tableau is best for (and what it is not).

           

          Yours,

          Yuri