VBA Function that Identifies Significant Value Shifts

R

robboll

I am looking for a function (Flag) that identifies Significant changes
based on previous values.

Example.

Flag shows TRUE if the row value above it is 100 or more points LESS
than the previous value.

Unit---Date----------Value-----Flag
A------1/10/2006---500--------FALSE
A------1/11/2006---499--------FALSE
A------1/12/2006---399--------TRUE
A------1/13/2006---200--------TRUE
A------1/14/2006---250--------FALSE
B------1/10/2006---900--------FALSE
B------1/11/2006---925--------FALSE
B------1/12/2006---750--------TRUE
B------1/13/2006---645--------FALSE
B------1/14/2006---644--------FALSE

Any help appreciated!!!

RBollinger
 
J

John Spencer

Field: Flag: DLookup("Value","YourTable","Unit=""" & [Unit] & """ AND [Date] =
#" & DateAdd("d",-1,[Date]) & "#") - Value > 99

Assumption is that the previous date is always one day before the date in the record.

Another way to do this would be in a query, that would look like this

SELECT T.Unit, T.Date, T.Value, (T1.Value - T.Value) > 99 as Flag
FROM YourTable as T INNER JOIN YourTable as T1
 
R

robboll

Thanks for the quick reply! I tried both and the first one doesn't
render any true or false values all null.

For the second I get the error "Join Expression Not Supported"

I will continue to work with it.
 
J

John Spencer

SELECT T.Unit, T.[Date], T.[Value], (T1.[Value] - T.[Value]) > 99 as Flag
FROM [YourTable] as T INNER JOIN [YourTable] as T1
On T.Unit = T1.Unit
AND T.[Date] = T1.[Date]-1

I did have a typo in the query I posted in the join
AND T.Date = T.Date-1
should have read
AND T.[Date] = T1.[Date]-1

By the way Value and Date should not be used as field names. They are both
reserved words and could cause you problems. Date is a function that
returns the system date. Most controls have a value property.
 
R

robboll

Cool -- I didn't think anything like this could be done without using a
function. My example wasn't as perfect as your response. What happens
if the data looks like this (with inconsistent dates):

Unit---Date----------Value-----Flag
A------1/10/2006---500--------FALSE
A------1/12/2006---499--------FALSE
A------1/15/2006---399--------TRUE
A------1/17/2006---200--------TRUE
A------1/28/2006---250--------FALSE
B------1/10/2006---900--------FALSE
B------1/11/2006---925--------FALSE
B------1/14/2006---750--------TRUE
B------1/18/2006---645--------FALSE
B------1/23/2006---644--------FALSE

John, Thanks for your help!

RBollinger
 
J

John Spencer

I'm not at all sure that this query will work, but I would try

SELECT T.Unit
, T.[Date]
, T.[Value]
, (T1.[Value] - T.[Value]) > 99 as Flag
FROM [YourTable] as T INNER JOIN [YourTable] as T1
On T.Unit = T1.Unit

WHERE T1.Date =
(SELECT Max(T2.Date)
FROM YourTable T2
WHERE T2.Date <= T.Date
AND T2.Unit = T.Unit)
 
Top