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

# Combined calculation

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

Sure.

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

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

• ###### 2. Re: Combined calculation

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.

Thank you,

David

• ###### 3. Re: Combined calculation

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

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