Bookmark and actual dymanic date

[george55]

Hi all, 

Would like to have a flag in a date filter pane, eg. 1 = the most actual available date. The formula below works fine:

=If(Datum = Date(Floor(ReloadDate)), 1, 0)

But when I want to use it as a bookmark, and have ticked "1" then it does not work properly. Reason: In the filter will be saved the last actual date, so this date will remain for every new date (eg. below 28.07.2020). 

Has anybody an idea how to implement it dynamicaly in the bookmark, so by clicking on it you always have the actual date?

Thanks.

[chrismarlow]

Hi,

I don't know if you had found this post that has a link to this YouTube video that seems to be what you are after.

So might you be looking at something like =Datum = Date(Floor(ReloadTime())) in your search bar initially?

Cheers,

Chris.

[george55]

Hi Chris,

Not exactly. Have a field called "ReloadDate" which represents the last reload date of the data. The ReloadDate changes everyday, as the job is daily.

"Datum" is a generic date from a Mastercalendar. Would like to filter in field "Datum" with the "ReloadDate". If works fine, but not as dynamic Bookmark. Also in my solution there is a mixup. See picture below, both date-fields are filtered simultaneously.

[chrismarlow]

Hi,

I see what you mean now, the dynamic bookmark saves the set analysis for settings and has just picked up the dates; 

I don't see a way of doing this in the UI (you would want the set filtering to look something like {<Datum={'$(=Date(Floor(ReloadDate)))'}>}), but there would be plenty of options on the scripting front, you could add a calculated field to your master calendar for example, if changing the script is an option.

Cheers,

Chris. 

[george55]

Hi Chris

Yes I believe now you did understand my issue.

My idea was to add flag with the max-Reload-Date e.g. = 1. Like I have already in my Mastercalendar for the actuall Month:

           If(InMonth($(pField), Today(), 0), 1, 0) as ActualMonth

 My thoughts about it:

1) My database which stores historical data, would be full of 1's, as everyday a 1 would be set. How can I then differentiate it for the last date?
2) Today() is not equal to ReloadDate. ReloadDate is not yet known in the Mastercalendar Script, as have many datasources with differents datamodels and different ReloadDates.

Thanks,

George

[chrismarlow]

Hi George,

You could reload your master calendar at the end of the script when you know the ReloadDate(s) & drop the original, so something like this (I've not tested below at all ... so may need a bit of debugging, I never get peek right :));

vReloadDate=peek('ReloadDate');
mastercalendar_new:
NoConcatentate
Load
   *,
   If(Datum=Date(Floor(vReloadDate)),1,0) AS ReloadDateFlag
Resident mastercalendar;
DROP TABLE mastercalendar;

 If you have more than one ReloadDate to consider, you would need more ReloadDateFlag fields, so it could become a bit unwieldy.

Cheers,

Chris.

[george55]

Hi Chris

Looks promising. Tried it out and have some issues.

Master will be imported at the beginning of the script with:
       $(Must_Include=[lib://10_QlikSenseData_$(vEnv)/10_Inc\Kalender\SubCalendar.qvs]);

Can not address it with 'Resident SubCalendar'. Do you know why? So I have done it like this:

vReloadDate = Peek('ReloadDate');
MasterCalendarNew:
NoConcatenate
Load *,
If(Datum = Date(Floor(vReloadDate)), 1, 0) as ReloadDate_Flag
FROM [lib://10_QlikSenseData_$(vEnv)/10_Inc\Kalender\SubCalendar.qvs]; 
Drop Table [lib://10_QlikSenseData_$(vEnv)/10_Inc\Kalender\SubCalendar.qvs];

Now have the error that 'Datum' is not known, what I don't understand. In the SubCalendar 'Datum' would be created with:

Date($(pField)) as Datum,

[chrismarlow]

Hi,

Is the table that the SubCalendar.qvs produces definitely called SubCalendar?

What do you get if you just run $(Must_Include=[lib://10_QlikSenseData_$(vEnv)/10_Inc\Kalender\SubCalendar.qvs]); with no other code?

Cheers,

Chris.

[george55]

Hi,

The name of the Master ist 'Calendar', sorry my fault.

Now I have the problem that 'vReloadDate' will not be found. Played around and have to say don't understand the Peek function. And also why using quotes '' ('ReloadDate')?

Have also to say that ReloadDate will be loaded and saved in a separate qvd:

Konto:
Load Distinct
...,
...,
...
ReloadDate
FROM [lib://10_QlikSenseData_$(vEnv)/50_Int\WIN\Konto.qvd] (qvd);

[chrismarlow]

Hi,

Attached should be a bit more like it (spotted a few typos).

Cheers,

Chris.