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.

       

      David

        • 1. Re: Combined calculation
          Joe Oppelt

          Sure.

           

          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,

             

            David

            • 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.

                 

                David