on 12-08-2009 1:10 PM
Hi All,
I have a real challange and I'm Struggling...
I need to calculate the number of days within a range over a number of 'Visit Dates'. The calulation needs work out the linear change over time (in days). Here is the example to best explain what I'm talking about:-
Example Table
Visit Date Value Range Low Range High
04/08/2009 1.7 2 3
18/08/2009 2.3 2 3
25/08/2009 2.6 2 3
29/09/2009 2.2 2 3
We have values from 4/8 to 29/9 ie 56 days
From 18/08 to 29/09 the values are all within the Low/High range so 42 days definitely in range
From 4/08 to 18/08 the value climbs from 1.7 to 2.3 in 14 days
So the rate in increase of the value is (assuming linearity)
is 0.6/14=0.0428571 units per day
So the value will hit 2.0 in 0.3/0.0428571 = 7 days
During these 7 days the value is between 0.7 and 2.0, therefore the value is below the target range (between 2 and 3), and for the next 7 days the value will be between 2.0 and 2.3 and is therefore in range
So overall, of the 56 days in question, 7 are below range and 49 are in range. This adds up to 87.5% of days in range.
I hope that makes sense and if anyone crack how to do it I will eternally grateful.
You show the math, so I'm not going to give formulas, but basically what you want to do is look at the current record and the previous record (skipping the first record). If both records' value is within range, the number of days within range is the difference in the dates. If both values are outside of the range, and on the same side (both too high or too low), zero days are within the range. Otherwise, calculate the difference in dates, the difference in value, the slope of the line (value difference divided by date difference), then loop to count the number of days that the resulting linear function (y = slope * x + b; where x = loop counter 1, 2, ... number of days in between the dates, b = value of previous record) is within range.
Use the Previous() or PreviousValue() functions to get the data for the previous record, and use DateDiff() to get the difference between dates. (See online help...)
Sum the results of all record pairs to get total days.
HTH,
Carl
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thanks for that...
Could you spare a little more time to help with the formula for this part:-
"loop to count the number of days that the resulting linear function (y = slope * x + b; where x = loop counter 1, 2, ... number of days in between the dates, b = value of previous record) is within range."
Cheers
Andy
Something like this (basic syntax):
dim slope as number
dim b as number
dim x as number
dim y as number
dim count as number
slope = ({value} - PreviousValue({value})) / datediff("d", PreviousValue({?date}), {date})
b = PreviousValue({Value})
for x = 1 to datediff("d", PreviousValue({Date}), {Date})
y = slope * x + b
if y >= {low range} and y <= {high range} then
count = count + 1
end if
next x
formula = count
This will actually work for all scenarios, it's just a lot of logic for cases where you know the answer (like both values are below the range = 0). If you're only doing this a hand full of times on the report, that probably won't matter much. But if you're doing it millions of times...
HTH,
Carl
User | Count |
---|---|
95 | |
11 | |
11 | |
10 | |
9 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.