Change from previous date's value

  • Thread starter Thread starter Nikos Koutantos
  • Start date Start date
N

Nikos Koutantos

I have a table with values for working days (no weekends or holidays). How
can I have a field calculate percent change of each value compared to
previous date's value?

Date Value Change
Fri 28/1/2005 100
Mon 31/1/2005 120 20%
Tue 1/2/2005 108 -10%
 
One more thing, there is also a field for clients, so I want to have change
from day to day for each client.
Client Date Value Change
A Fri 28/1/2005 100
B Fri 28/1/2005 200
A Mon 31/1/2005 120 +20%
B Mon 31/1/2005 140 -30%
A Tue 1/2/2005 108 -10%
B Tue 1/2/2005 175 +25%
 
I have a table with values for working days (no weekends or holidays). How
can I have a field calculate percent change of each value compared to
previous date's value?

Date Value Change
Fri 28/1/2005 100
Mon 31/1/2005 120 20%
Tue 1/2/2005 108 -10%
Click to expand...

You'll need a Subquery to find the most recent previous record. Note
that Date is a reserved word and not a good name for a field. Try:

SELECT [Client], [Date], [Value], [Value] - (SELECT [Value] FROM
yourtable AS X WHERE X.[Client] = yourtable.[Client] AND X.[Date] =
(SELECT Max([Date]) FROM yourtable AS Y WHERE Y.[Client] =
yourtable.[Client] AND Y.[Date] < yourtable.[Date])) / [Value] AS
PctChange FROM yourtable;

John W. Vinson[MVP]
 
Back
Top