6 Replies Latest reply: Aug 2, 2010 5:42 PM by John Witherspoon RSS

    Selecting a date range when using QVPX


      I have a table with a date column, I'm also using this date column in a list box. What I want to do is allow the user to select a date range (using a from/to date selectors instead of showing all available dates).

      Right now I have to get the entire list of available dates to check which dates are in the range and then select these items in the listbox based on their index.

      Is there a simple way to select a date range by passing in two dates?


        • Selecting a date range when using QVPX
          John Witherspoon

          I'm not sure there's a simple way. It sounds like you have from and to date variables that the user enters, and then you run a macro to select the dates in that range. Now, if I'm understanding how you wrote the macro, there's probably a simpler way. You should be able to select the dates using a search expression rather than looping through them. I can't remember the syntax, so you'd need to check the API guide. Not simple, but at least simpler.

          Another approach is to use a multi-value slider for the Date. The user slides the end points to the dates they want, and QlikView automatically selects everything in between. But that can take an annoying amount of screen real estate, and may not even be possible if you have too many dates.

          If the selected date ranges follow a pattern, like YTD, or a quarter, or what not, you can always make a calendar that ties those together and give them extra list boxes. But I'm guessing you need to go from ANY date to ANY other date.

          I assume there's some way to select by search expression as an action in 9.0, but I don't know for sure, and don't know if you're on 9.0.

          So variables and a macro may well be the best solution.

          • Selecting a date range when using QVPX

            If the date ranges are somewhat typical wouldn't it be an idea to stick to the "built in logic" of Qlikview just having the user selecting the periods with regular fields?

            That seems to be more stringent with the way qv works in general to avoid confused end users/




              • Selecting a date range when using QVPX

                Ideal would be something users see in all other reporting solutions. select from date and to date.

                If you have daily data for multiple years, selecting from that list will be hard and annoying when a user wants to see 3 months of data. picking 90 items out of 700 isn't going to be easy and it's not something I would want my end users to be forced to do.
                The other option I saw in samples on qlik.com is to have buttons to select days/months/years. The problem is that if you select day 5, it shows data for that day in all months and years, not exactly expected behavior.

                Selecting a date range for data is the most common feature in reports, I'm really surprised QV doesn' t have an implementation of it. I haven't seen a report that doesn't include that.

                We were forced to do a workaround for it, which makes everything harder.

                  • Selecting a date range when using QVPX
                    John Witherspoon

                    I agree that QlikView should more natively support selecting an arbitrary date range. I agree that it's silly that it doesn't, and is annoying to work around.

                    However, if "a user wants to see 3 months of data", that is trivial to do in the example FHoeglund gave. Just pick the three months of data. To me, it's simpler to drag select "March 2009, April 2009, May 2009" than to make the user select "March 1, 2009" from one calendar and "May 31, 2009" from a second calendar, or to type them in manually, or whatever. Every application I have delivered to my users has used the same basic approach that FHoeglund gave. Our users generally don't want some arbitrary date range like March 22, 2007 through February 17, 2008. They want something much simpler, like the previous quarter, or May 2009. Two calender objects to select a date range would simply be in the way 99% of the time.

                    Your needs and your users may differ of course, but a simple calendar with simple fields (month, year, etc.) has always worked for us.