List Column Calc using row data 1

[scullysiz]

I am trying to calculate total miles for a vehicle by taking the odometer reading from the current week and subtracting the odometer from the prior week. The problem I am having is that the data is in a list report and each week is a row of data. Do you have any ideas on the best way to calculate the total number of miles, knowing that there will be new data added each week? Below is an example of the data:

Row# Vehicle Tag # Wk st dt Wk end Dt End Odm Read Tot Wkly Miles
1 2345 5/5/11 5/11/11 10,100 n/a
2 2345 5/12/11 5/18/11 10,500 400


I am trying to figure out how to calc this dynamically for each week. Eventually, this will be a query within another report, which is why it is in list format.

Thank you,
Susan

 

[blom0344]

Check out the running difference function. You need to line up data properly for this to work..

Ties Blom

 

[scullysiz]

I got that function to work after doing a CAST function on the data value. Thanks for the help!

 

[Georgia1]

Hello, I am trying to do something similar, maybe you guys can help,

I am trying to develop this in report studio:
if (Contract_Nb != Next(Contract_Nb)) {1 }

Thanks!!

 

[blom0344]

You may look into the lag / lead functions. Some RDBMS support these analytical functions. You do not mention whether you are working with a dimensional or relational model, so difficult to advise

Ties Blom