2 Replies Latest reply on Jun 1, 2015 8:00 AM by Eric Decker

# Table Calculation referencing previous row and current row data

Hello All!

I’m trying to add a table calculation in a crosstab that will compute the current row’s Total Parts multiplied by the previous row’s First Pass Yield (where applicable) minus the product of the current row’s total parts and current row’s First Pass Yield.

i.e. Calculation2 =(([Total Parts] * ([First Pass Yield] of previous period)) - ([Total Parts] * [First Pass Yield])

Unfortunately I cannot add the packaged workbook since the data is sensitive, but all the information is included in the screen shot with the examples of what the results of the calcs should be. I’ve dabbled with some default calculations and playing around with computing by table down, pane down, etc but I can’t seem to quite get the numbers where I need them. Any help is much appreciated!

Thanks,

Eric

• ###### 1. Re: Table Calculation referencing previous row and current row data

Sounds like you should try the LOOKUP function (with a -1 as the offset):

Calculation2 =(([Total Parts] * (LOOKUP([First Pass Yield],-1))) - ([Total Parts] * [First Pass Yield])

.. to be certain .. I would create a calculated field of the previous [First Pass Yield] like this:

Previous First Pass Yield = LOOKUP([First Pass Yield],-1)

• ###### 2. Re: Table Calculation referencing previous row and current row data

Jason,

Thank you so much! I was trying to do some stuff with the LOOKUP() function too, but I think I was then trying to do additional calculations across the table/pane (or both) which clearly wasn't the right thing to do.

This worked like a charm, and now I have this in my back pocket. This type of function is really powerful and will definitely come in handy in future reports!

Thanks again!

Eric