17 Replies Latest reply: May 12, 2009 11:56 AM by Peter Turner RSS

    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.
        • How to Filter data on screen with a variable
          Oleg Troyansky

          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
              John Witherspoon

              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

                  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
                      John Witherspoon

                      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
                  Michael Solomovich

                  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
                      John Witherspoon

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



                      • How to Filter data on screen with a variable
                        Michael Solomovich

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