topic Dynamic date selection using bookmarks in QlikView

Dynamic date selection using bookmarks

Fri, 17 Jun 2011 09:15:26 GMT

Is there a way to include a variable date selection in a bookmark ?

We have a Qlikview file that contain dates and registered hours for a number of people.

On a weekly basis, registered hours are analysed.

We want to create a bookmark that will select the following date range :

=> Select last week (7 days starting from yesterday)

=> Select from beginning of the month untill yesterday

Can it be achieved using a bookmark ?

I do not want to use set analysis as I want the selection to be valid for my complete QV file.

Setting the date range using a macro function is not really an option as different scenario's may exist (beginning of month, last week, last fortnight, ...)

Dynamic date selection using bookmarks

pat_agen — Fri, 17 Jun 2011 09:23:19 GMT

hi,

are you able to write the code for your two selections?

if you can do this try the following.

click on a list box containing your dates. Click on search or just start typing. Make sure your search query begins with the equal sign "=" followed by the code for your select. You will know if it is working because the corrcet dates will be selected.

Save this as your bookmark.

Qv will store your search query in the boomark and not the dimesnion elements this returns. thus making your bookmark dynamic.

To check this open a current selections object and select your bookmark.

Dynamic date selection using bookmarks

Fri, 17 Jun 2011 12:53:45 GMT

My formula should be something like this :

=<today()-1 > today()-8

However, even the function = today() selects too much data. I would expect the system to select only today's date in this case but it doesn't. I want to select all the dates between yesterday and 7 days before yesterday by means of a dynamic select.

Dynamic date selection using bookmarks

pat_agen — Fri, 17 Jun 2011 13:40:36 GMT

try something like this in the search box, replace "Timestamp" with the name of your date field

=if(floor(Timestamp)>floor(today(2)-8) and floor(Timestamp)<floor(today(2)) ,1)

the parameter 2 inside the today() function means it will take the time teh document was opened so it shouldn(t be constantly evaluating itself.

another alternative is to set a vraible i your script - especailly if the data is getting reloaded everyday.

the floor() functions get around problems caused by formatting and the bits of days you get in timestamps.