Rate of change

[#1]

Hello There,

In the sample table below I'm looking for an expression that is able to determine if within an hour, the value of 'Measure 2' has exceeded 100. In other words the cell with the value exceeding 100 would be highlighted if it is within an hour. At the same time if the average value within an hour exceeds 150 at any given time the cell would also be highlighted.

Date	Time	Measure 1	Measure 2
08/07/2016	00:00:03	181.00	66.90
08/07/2016	00:00:14	182.09	65.02
08/07/2016	00:01:03	264.11	64.66
08/07/2016	00:02:03	198.22	93.01
08/07/2016	00:03:03	277.05	100.01
08/07/2016	00:04:03	287.43	78.91

So for example if we take the Time as 00:00:00, 01:00:00, 02:00:00, 03:00:00 and 04:00:00, if the value of 'Measure 2' is 178 at 00:00:00 and 125 at 01:00:00 the change would be 53 which means the value has not breached the 100 target although both values are above 100. At the same time the average value within that hour is 151.5 (i.e. ave of 178 and 125) which breaches the 150 threshold.

I'm thinking there would have to be a some sort of rate of change expression combined with the if statement you have suggested?!? Linest_M function?

Any ideas please?

[shun_wong]

Hi Mike,

You can do this by adding an expression to the background colour...

and use if statements...

Let me know if this does the job...

[#3]

You are right in terms of writing it in the background color however, the difficulty arises when you only want this to be highlighted within an hour. What you have suggested would highlight the value irrespective of the time. So for example if we take the Time as 00:00:00, 01:00:00, 02:00:00, 03:00:00, 04:00:00, 05:00:00, 06:00:00,

if the value of 'measure 2' is 178 at 00:00:00 and 125 at 01:00:00 the change would be 53 which means the value has not breached the 100 target although both values are above 100. At the same time the average value within that hour is 151.5 (i.e. ave of 178 and 125) which breaches the 150 threshold.

I'm thinking there would have to be a some sort of rate of change expression combined with the if statement you have suggested?!? Linest_M function?

[shun_wong]

Apologies,

If you have a look at the RangeSum function, this should sort out this issue.

If you have having troubles with this, attach a sample app and I can show this to you.

[jonathandienst]

Let me see if I have this right - you want to highlight those items where:

• The average value in an hour exceeds 150
• The rate of change in an hour (end - start) exceeds 100 (absolute value?)

You would need to decide how you want to handle irregular time intervals between readings for both of these measurements; you would need a master calendar or master time table that includes hours as a field.

The rate of change probably does not need to use Linest_M, you could calculate the rate directly with the expression:

-  (value at end - value at start) / (time at end - time at start)

[#6]

Jon,

Yes, that's right and yes, I believe (value at end - value at start) / (time at end - time at start) is correct. But what is the appropriate expression to represent this? 'Measure 2' is a calculated field so I'm not sure how to apply this to it. I'm thinking the dependency should be time?!

[jonathandienst]

I suggest that you upload a small sample qvw with some representative data to get more help. You might want to move the calculation for Measure 2 to the load script (at the lowest level of granularity), so that you are working with a field rather than an expression.