VBA Function that Identifies Significant Value Shifts

  • Thread starter Thread starter robboll
  • Start date Start date
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
 
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
 
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.
 
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.
 
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
 
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)
 
Back
Top