How to Filter data on screen with a variable

[peter_turner]

Hello All!

My data deals with event messages being stored every few seconds into SQL.

I would like to have a 'Hour_Filter' list box that I can select a value from, to filter my data down to show only values in the last 2 hours from now.
I've made an inline table such as:

Hour_Filter:

load * inline [

Hour_Value, Text

1, "1 Hour"

2, "2 Hour"

4, "4 Hour"

8, "8 Hour"

];

My data is loaded with the following typical fields:

Data,

TimeDate,

Date(TimeDate) as myDate,

Time(TimeDate) as myTime,

Hour(TimeDate) as myHour,

Mins(TimeDate) as myMins,

Etc...

If I select the "2 Hour" option on screen, I would like to-do something like this:

Let oldHour=time(now())-Hour_Value (from the selected item on the list box, time would be -2 hours ago)

Let currentHour=time(now()) (current time mark)

for each row of data in my table

Show row if myHour>= oldHour

And

myHour <= currentHour

The problem is that I don't want to reload my database to apply this filter. In effect I'm mimicking what a user would manually select from a list of hours on screen.

Any thoughts / inspiration would be much appreciated 🙂

Peter.

[rwunderlich]

These are great bits of coding. However, I'd like to caution that the use of now() in a chart expression is generally a bad idea from a performance point of view. It generates a fairly constant CPU activity.

-Rob

[johnw]

I'm going to guess that now(0), the time of the load, would be fine in this case. Since we're looking at near real time data, I assume this file is being reloaded very frequently, such as every ten minutes. If the data is 9 minutes stale, I would think when you ask for something in the past two hours, you'd be fine with getting something in the past two hours and 9 minutes, and nothing for the past 9 minutes.

[Anonymous]

Oleg, feel free to post on Wiki. Frankly, I didn't think it's something special. If I have time and don't forget, I'll try to describe a really complex case.
Rob, you're right abot the now() function. I thought of my example more like the idea rather than ready-to-use solution.
John, you're correct about the now(0). I never thought about it. In any case it's not fair to expect theat QV application provides the real-time data.

[peter_turner]

Hi Everyone,
Thanks to Michael, John, Oleg for you inputs, last few questions to finish this off...

1. I also like Michael's Timefilter.qvw, but performance will be important to me (new thread next week on that) so i tryed the set analysis example and replaced the
if(Time>=now()-Input/24,1)
with
sum({<Time={">=$(=timestamp(now()-Input/24))"}>} 1)
but i don't get any results, I'm still on a learning curve so not sure where the problem is on that statement.

2. I can get my data to filter (with the if statement) to a 2/4/8 hour period as needed,
But i also have month, date, hour, min etc list box fields on screen which are used to show&select times and dates.

Is it possible to have these list box's highlight to reflect I'm viewing a particular hour period?
So if the time/date was 11/5/2009 12:33:00 and i was viewing a 2 hour period, I'd like my year/month/day highlight,
the hours 10,11,12 and all the mins highlight if possible.

Thanks,
Peter.

[Oleg_Troyansky]

Peter,

to highlight the selected period in the list boxes, you have to use "Selection" functionality and not "Set Analysis". Michael's example with the calculated List Box is the best example of how to use it. Repeating Rob's advise, - using now() might negatively affect your performance, since it triggers constant "action" by the timer. You should think of replacing now() with some other variables, or at least use now(0) which will give you the time of the reload.

cheers,

Oleg

[peter_turner]

Hi Oleg,

Thanks for the input, i'm using the reload time as a fixed point for my hour periods.

Very last question, when i select a time 'window' , i want everything from x hours ago to present.
The ceil(((now(0)-Time)*24) gives me only a 1 hour time frame starting x hours ago.

Thanks,
Peter.

[rwunderlich]

Peter,

If you are using reload time as the fixed time, you may want to pre calculate in the script instead of in the charts. Attached is a modification of a QV Cookbook example that lets you assign Times to groups like "1 Hour" or "Today".

-Rob

[peter_turner]

Thanks to everyone who's helped out on this thread!

The example Rob posted worked perfectly for my application, and the other examples have tought me some new things too.

Thanks,
Peter.