Calculate Linear Change (CR XI)

[Former Member]

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.

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

Thanks Carl, it works.

That's been a massive help, I really appreciate your time on this!!!

[Former Member · Accepted solution]

Hi Carl,

Just one more quicky (my Basic knowledge is very limited)...

If the following result = 0, how do you write in the Basic code to mitigate 'Division by 0'

( - PreviousValue()) / datediff("d", PreviousValue({?date}), )

Thanks

Andy

Edited by: Andrew Wear on Dec 10, 2009 12:43 PM

[Former Member · Accepted solution]

Wrap the entire logic above in (basic syntax):

if datediff("d", PreviousValue(date}), {date}) = 0 then
  formula = 0  ' We don't want to double-count days
else
  < formula above >
end if

HTH,

Carl

[Former Member · Accepted solution]

Once again, thanks....