Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Not applicable

Rate of change

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.

DateTimeMeasure 1Measure 2
08/07/201600:00:03181.0066.90
08/07/201600:00:14182.0965.02
08/07/201600:01:03264.1164.66
08/07/201600:02:03198.2293.01
08/07/201600:03:03277.05100.01
08/07/201600:04:03287.4378.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?

Tags (1)
6 Replies
Partner
Partner

Re: Rate of change

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...

Not applicable

Re: Rate of change

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?

Highlighted
Partner
Partner

Re: Rate of change

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.

MVP
MVP

Re: Rate of change

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)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Re: Rate of change

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?!

MVP
MVP

Re: Rate of change

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein