I have a single field named Aged, which has several possible values:
if (dayStart(timestamp(DUEDATE)) - dayStart(Today()) > 90, 'Due Now',
if (dayStart(timestamp(DUEDATE)) - dayStart(Today()) >= 0 AND dayStart(timestamp(DUEDATE)) - dayStart(Today()) <= 7, 'Due Soon',
if (dayStart(timestamp(DUEDATE)) - dayStart(Today()) >= 8 AND dayStart(timestamp(DUEDATE)) - dayStart(Today()) <= 14, 'Not Due Yet'))) AS 'Aged'
I have ten document bookmarks which I want to add the Aged field to, as a filter, but only for two of the possible values - Due Now and Due Soon. Each bookmark also has other bookmark filters - for example, I want one of the ten bookmark to have filters like this:
Business='Automobiles'
Country='UK'
Aged='Due Now','Due Soon'
The other nine bookmarks would have different values for the Business and Country fields, but I need to add the Due Now and Due Soon values for the Aged field to all ten bookmarks.
The underlying data changes every day - for example, the UK Automobiles bookmark could have data that was Due Now or Due Soon yesterday, but no data that is Due Now or Due Soon today, which means I cannot add the Due Now and Due Soon values as "regular" bookmark filters because if no data is found that is either Due Now or Due Soon, Qlikview will clear the other filters (Business and Country, in this case) in order to return data. I need a way to add the Due Now and Due Soon values as filters without the risk of losing the other bookmark filters.
Is there a way to set a dynamic bookmark filter in this situation? Some way of determining - before the bookmark is run - whether or not Due Now or Due Soon data exists in each case? Perhaps as code in the load script, or as a variable? The ten bookmarks are also attached to alerts, so could something be done within the Message expression in the Alerts dialog for each bookmark?
