topic How to Filter data on screen with a variable in QlikView

How to Filter data on screen with a variable

peter_turner — Thu, 07 May 2009 21:06:01 GMT

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.

How to Filter data on screen with a variable

Oleg_Troyansky — Thu, 07 May 2009 21:14:31 GMT

Peter,

since you don't want to reload the data, the only 2 options that I can see (within the current version) are:

1. Use a Macro to forse time selection when the user is selecting a time range.

2. Use Set Analysis modifiers in all of your functions, to limit the data to the time range.

In the next version you'll have a couple of new options - force selections using Actions (instead of Macro) or modify your data using Macros without Reload.

I have a feeling that there should be a more elegant solution, but I can't see it at the moment...

Oleg

How to Filter data on screen with a variable

johnw — Thu, 07 May 2009 23:08:14 GMT

While it isn't specifically what you're asking for, an additional option would be a multi-value slider. It's more flexible and requires neither macros nor set analysis.

How to Filter data on screen with a variable

peter_turner — Fri, 08 May 2009 17:14:54 GMT

Hello John,

That's in interesting idea...
When i set the multi-value slider 'Date - Field' option to be my EventHour time field, i can manually select my hour ranges.

When i set 'Date - Variables', i can make the slider select the required hour time range.

But i'm unable to combine the two steps. When i use the variables option i need to somehow link it back to my EventHour field to effect the selection, but i'm not sure how?

For my variables i used:
=hour(now())- Only(Hour_Select_Field)
=hour(now())

Thanks,
Peter.

How to Filter data on screen with a variable

johnw — Sat, 09 May 2009 00:17:16 GMT

I wasn't thinking variables at all. I was thinking that you'd have some DateHour field connected to your data, and use the multi-value slider to set it directly. If they want the past two hours, they just set the range that way. If they want a three hour period two hours ago, they can do that as well.

But with a little further thought, what I was thinking just wasn't going to work. It looks like we can't use a static min and max for a field, or a logarithmic scale, or anything else that would make these selections manageable. So in practice, the increments would be so small that it would be impossible to select the past two hours.

Another option would be a simple descending order list box for the DateHour field. Make it large enough to show the past 8 hours. It would then be a simple matter to select the desired hour range from the list box.

How to Filter data on screen with a variable

Anonymous — Sat, 09 May 2009 00:35:03 GMT

Peter, see attached. Is it close enoughg to your rrequirements? Notice that there is no need for separate Hour field, I"m using only timestamps.
As for the presentation - sure you can use slider instead of the input box, and remove constrains on variable.

How to Filter data on screen with a variable

johnw — Sat, 09 May 2009 01:01:34 GMT

Can't speak for Peter, but I like it. For performance, I'd use set analysis instead of an IF.

sum({<Time={">=$(=timestamp(now()-Input/24))"}>} 1)

The downside is that it only applies to that one chart, or any chart where you use the expression. No actual selection is being made. If that's not an issue, it's seems like a good solution to me.

How to Filter data on screen with a variable

Anonymous — Sat, 09 May 2009 02:04:19 GMT

Thanks John,

I use set analysys occasionally, but can't force myself to like its cumbersome syntax.
Actually I thought Peter wanted variable (?) Another way is to use calculated list box. In the attached example there are two - first selects times, the second selects Activities.
Whatever user wants...

How to Filter data on screen with a variable

johnw — Sat, 09 May 2009 02:47:34 GMT

Oooh! Nice one! Wish I'd thought of it.

How to Filter data on screen with a variable

Oleg_Troyansky — Sat, 09 May 2009 04:23:54 GMT

Nice one, Michael! Very slick !

Would you like to write a Wiki on this topic? If you won't, I'll write one

Oleg

How to Filter data on screen with a variable

rwunderlich — Sat, 09 May 2009 04:26:07 GMT

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

How to Filter data on screen with a variable

johnw — Sat, 09 May 2009 04:31:08 GMT

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.

How to Filter data on screen with a variable

Anonymous — Sat, 09 May 2009 06:41:55 GMT

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.

How to Filter data on screen with a variable

peter_turner — Mon, 11 May 2009 18:37:09 GMT

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.

How to Filter data on screen with a variable

Oleg_Troyansky — Mon, 11 May 2009 20:14:00 GMT

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

How to Filter data on screen with a variable

peter_turner — Mon, 11 May 2009 21:40:51 GMT

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.

How to Filter data on screen with a variable

rwunderlich — Tue, 12 May 2009 02:05:53 GMT

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

How to Filter data on screen with a variable

peter_turner — Tue, 12 May 2009 15:56:34 GMT

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.