topic Re: Including variable in bookmark in QlikView

Including variable in bookmark

Thu, 17 Apr 2014 15:01:52 GMT

Hi,

I am having some trouble including a variable in a bookmark.

I have a set of ticket data, with date fields. I want the bookmark, which will have other selections I'll specify to create the bookmark, to pull the last 12 months of data.

Example: The month is April 2014. I want to click the bookmark and have it apply the filters I had selected, but also pull all incidents from March 2013 through March 2014.

We are now in May 2014, I want to click the bookmark and have it apply the filters I had selected, but also pull all incidents from April 2013 through April 2014.

Please let me know if this is possible.

Re: Including variable in bookmark

swuehl — Thu, 17 Apr 2014 18:22:34 GMT

Use a search expression to select your Month field, where you use the current date to derive the selection from, something like

=DateField<monthstart(today()) and DateField >=monthstart(today(),-13)

Enter something like this in the search bar of your DateField field, this should select the appropriate range.

If you then create a bookmark, you are actually bookmarking the search, so when you call the bookmark next month, the range will be adapted accordingly using the date you are calling the bookmark.

Re: Including variable in bookmark

Thu, 17 Apr 2014 18:38:35 GMT

Hi,

This did not quite work.

I took the formula you provided and substituted my date field( [Submit Date] ). I placed this as an expression of a list box and as a search object. When I placed it in the expression for the list box it pulled the previous 13 months which is good. It pulled March 2013 to March 2014. I created and saved a bookmark.

I then added new "sample" data for April 2014 to simulate the passing of a month, so that we are looking for April 2013 to April 2014. I refreshed the app, and the bookmark still pulls March 2013 to March 2014.

Re: Including variable in bookmark

swuehl — Thu, 17 Apr 2014 18:44:51 GMT

The expression is using today() to determine the date range, and this is probably not affected by your new data set.

You would need to wait some time to create a different date range in your search.

For testing, you can try using the max date in your data instead of today().

edit:

Maybe something like

=DateField<monthstart(may({1} total DateField)) and DateField >=monthstart(may({1} total DateField),-13)

Re: Including variable in bookmark

Thu, 17 Apr 2014 19:08:07 GMT

Hi, something is wrong with that syntax. Not working.

I tried

=[Submit Date]< monthstart(max([Submit Date])) and [Submit Date] >=monthstart(max([Submit Date]),-13)

No errors, but the list box returned no values when I put it in as an expression.

Re: Including variable in bookmark

swuehl — Thu, 17 Apr 2014 19:21:38 GMT

You probably corrected my typo, should be max() function:

=DateField<monthstart(max({1} total DateField)) and DateField >=monthstart(max({1} total DateField),-13)