Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
george55
Partner - Creator III
Partner - Creator III

Bookmark and actual dymanic date

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)

2020.0730-132437.jpg

 

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). 

2020.0730-132650.jpg

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

Thanks.

Labels (2)
1 Solution

Accepted Solutions
george55
Partner - Creator III
Partner - Creator III
Author

Hi Chris,

I found the solution.

Have done a Left Join to my Mastercalendar with a new Flag and implemented a Max.

Mapping Load Distinct
    Floor(Max($(pReloadDateField))),
   1
FROM [$(....)] (qvd);

 

Cheers
George

View solution in original post

12 Replies
chrismarlow
Specialist II
Specialist II

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
Partner - Creator III
Partner - Creator III
Author

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.

 

george54_0-1596443261063.png

 

chrismarlow
Specialist II
Specialist II

Hi,

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

20200805_1.png

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
Partner - Creator III
Partner - Creator III
Author

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
Specialist II
Specialist II

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
Partner - Creator III
Partner - Creator III
Author

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
Specialist II
Specialist II

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
Partner - Creator III
Partner - Creator III
Author

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
Specialist II
Specialist II

Hi,

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

Cheers,

Chris.