Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
peter_turner
Partner - Specialist
Partner - Specialist

How to Filter data on screen with a variable

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

Accepted Solutions
Anonymous
Not applicable

Thanks John,

I use set analysys occasionally, but can't force myself to like its cumbersome syntax. Tongue Tied
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...

View solution in original post

17 Replies
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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

johnw
Champion III
Champion III

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.

peter_turner
Partner - Specialist
Partner - Specialist
Author

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.

johnw
Champion III
Champion III

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.

Anonymous
Not applicable

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.

johnw
Champion III
Champion III

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.

Anonymous
Not applicable

Thanks John,

I use set analysys occasionally, but can't force myself to like its cumbersome syntax. Tongue Tied
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...

johnw
Champion III
Champion III

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

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

Nice one, Michael! Very slick !

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

Oleg