4 Replies Latest reply on Sep 25, 2017 3:09 PM by David Recuero Guerra

    Combined calculation

    David Recuero Guerra

      Hi all,


      Please refer to the example below in Excel. My dataset contains Dates and Returns, and I am trying to calculate column C in Tableau. The first value of ''Returns'' is always 1, so is the first value of ''Calculation''. The next values of ''Calculation'' are done as follows:


      For example, in Jan:  C2= B2 or C2= 1

                            in Feb:  C3= B3*C2

                            in Mar:  C4= B4*C3 and so on.



      Do you know if it is possible to do this combined calculation?


      Thank you very much.



        • 1. Re: Combined calculation
          Joe Oppelt



          LOOKUP( SUM([C]), -1)


          will get you the [C] value for the prior row.

          • 2. Re: Combined calculation
            David Recuero Guerra

            Hi Joe,


            Thank you for your answer. Actually I think it's a bit more complicated than that, since the calculation is not just getting the C value for the prior row but multiplying B* C value for the prior row, as in the example above.


            Please find attached the Tableau workbook for your convenience.


            Thank you,



            • 3. Re: Combined calculation
              Joe Oppelt

              Well sure, I just showed how you can get to a value from a different row.  But you can use that in any sort of math you want.  I wasn't clear on what you actually want to do, so I did a bunch and added them to your sheet.  (See sheet 2.)


              I'm reading that you want to multiply the B and C from the previous row, so I did it two ways in the two [Calculation 3] calcs.

              See attached.


              PS:  No value shows up in January in these calcs because there is no -1 for January, of course.  You can modify the calc to say:


              IF INDEX() = 1 then 1 else (put the actual logic here)...

              • 4. Re: Combined calculation
                David Recuero Guerra

                Thank you Joe, actually I didn't explain the problem clearly, my apologies, I will try to be more precise.


                Initially, my data source has ''Date'' and column ''B'', and the goal is to create a formula in Tableau that equals column ''C''. In Excel, I calculated column C assigning 1 to January (first value in the partition), and then multiplying sequentially B and C as shown below


                Feb B * Jan C = Feb C

                Mar B * Feb C = Mar C


                This kind of sequential calculation is easy to do in Excel at a cell level, but I don't know if Tableau can handle it as well.


                Please use the attached workbook instead of the original. I also attached the Excel file where you can see the formula for C.


                Sorry for the confusion and thank you again.