Dynamic Date Range Bookmark

[nhoover]

Support Partners,

  I found this Dynamic Bookmark video https://www.youtube.com/watch?v=tFyqOigqnUw this looks great so I can set up a bookmark to capture values from a certain date range after the launch of a promotion. I’m trying to do something similar to the video but am not able to get it to filter properly, I’m thinking it has to do with the date section 3/15/2022 (see below).

I tried several different ways:

= [Created Date] >= today()-2     #This one works but I need to start at a specific date of 3/15/22

=[Created Date]>=Date#(3/15/2022) AND [Created Date]<=Today()

=[Created Date]>=Date(3/15/2022) AND [Created Date]<=Today()

=[Created Date]>=3/15/2022 AND [Created Date]<=Today()

=[Created Date]>=’3/15/2022’ AND [Created Date]<=Today()

=[Created Date]>=3/15/2022

A small sample of my data is below, where the above filter should only return the last row since the first two are in the year 2021:

Created Date	Probability	Product Name	Opportunity Owner	Stage	Age
1/11/2021	80	Product A	Owner A	Cultivate	575
6/29/2021	40	Product B	Owner B	Closed Lost	183
6/20/2022	100	Product C	Owner C	Cultivate	50

Thank you so much for any and all replies!

[rwunderlich · Accepted solution]

I would write it as:

=[Created Date]>=MakeDate(2022,3,15) AND [Created Date]<=Today()

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[nhoover]

Thank you so much for the reply, I put the formula into the list box and hit enter. Unfortunately, the formula doesn't allow me to continue, it just stays in the list box and does not filter anything.  My goal is to use this as a dynamic bookmark so that when new data is added the bookmark will always show everything greater than 3/15/2022. Do you have any other ideas on how I might solve this problem? All the best and thanks again!

[Or]

From your screen capture, it looks like you've made a selection on the date which only includes two dates - 2021-02-08 and 2021-02-09. Since both of these are outside the range of "greater than 3/15/2022", your expression returns no values and can't be applied. Try applying it with the existing selection removed.

[nhoover]

Thanks for the suggestion, in order for the listbox to appear at the top I have to make a date selection. I tried selecting 8/8/2022 first. Then pasting in the formula: 

=[Created Date]>=MakeDate(2022,3,15) AND [Created Date]<=Today()

When I hit the Enter Key this time the listbox disappears and nothing is filtered. Thanks for taking a look at my problem!

[Or]

You can apply the selection from the Selections screen or from a filter box, so it's not necessary to pre-select anything (and in fact it is a good idea not to pre-select anything).

Working with dates in selection formulas is always a bit fiddly - I tend to just mess around until I get it to work. Start with only one of the two sides of the expression and see if each of them works individually (You can tell if it works by whether or not you get anything in the list of values when you type the formula in, so it's not necessary to hit enter). If they don't work, you may need to reformat the date values (MakeDate() and Today()) so they match the format of Created Date.

[nhoover]

Thanks again, I'm sure I'll figure this out, thanks for the suggestions. I'll post back here once I come up with a solution.

[nhoover]

Your solution worked after I understood I needed to be working in the "Selections Tool" section without having any selections on my date field. Thanks again for the speedy reply and aid in my learning!

[nhoover]

Thanks for leading me in the right direction, after your advice I was able to use a previous post as the ultimate answer.