Applying multiple dynamic dates for a burst report

[Philip_Z]

Hello, I need some help adding multiple dates as selections to my sheet in a burst report.

First off, I am using the "Send a burst report to email recipients from a straight table" as described in https://community.qlik.com/t5/Knowledge/How-to-send-a-bursted-report-using-Qlik-Application-Automati... 

Looping through the list and applying selections based on the list and sending out corresponding emails to corresponding recipients works without problems.

However, I also need to apply dynamic date ranges to the selection. Those are the same for each recipient.

In my sheets, I use bookmarks to apply the date ranges.
The bookmark is set to use formulas that select all dates between two dates.
I use three alternate states for the Date ranges (last 4 weeks, 4 weeks before that and 2019) and apply each of them with it's own formula.

=Date(Floor([bookingDate_leistung]))>= Date(Floor((vLast4WeekStart))) AND 
Date(Floor([bookingDate_leistung]))<= Date(Floor((vLast4WeekEnd)))

where the variables vLast4WeekStart and vLast4WeekStart are defined like this:

 let vLast4WeekStart = date(WeekStart(Today(),-4),'DD.MM.YYYY');
 let vLast4WeekEnd = date(WeekEnd(Today(),-1),'DD.MM.YYYY');

the variables for the 2019 Date range are a bit more complicated:

let v2019Year4WeekStart = MakeWeekDate(Year(WeekStart(Today(),-4))-3, Week((WeekStart(Today(),-4))), WeekDay((WeekStart(Today(),-4))));
let v2019Year4WeekEnd = MakeWeekDate(Year(WeekEnd(Today(),-1))-3, Week((WeekEnd(Today(),-1))), WeekDay((WeekEnd(Today(),-1))));

I tried using the "Apply Bookmark" Qlik Cloud Service, however this does not affect the selection for the Qlik Reporting Service and I read in another thread that you would have to apply the selections with the "Add Selection to Sheet" reporting service.

I am a bit lost on how to approach this.

Is there any way to set those dynamic dates in the Qlik reporting services and loop between them in order to apply all needed dates to the selection?

Thanks,
Philip

[Philip_Z · Accepted solution]

Hi everyone,

just letting you know, after a lot of trial and error and researching, I finally found a solution 😀.
As there might be others having problems with this, I will share the details with you.

The solution I found to this problem is pretty much what I was asking for in the first post:

1.  Grab the Variable values for the Timeframes from the Qlik App with the "Get Variable" Qlik Cloud service.
2. Make a loop, starting from the first date and incrementing by one day until the end date is reached and write the values to a list variable.
   • Format the list output in a way that each value is in the correct numerical format so that it can be applied to the date Field.
3. Apply the formatted list output to the selection as Raw input

Detailed Solution:

Step 1.

Get the Variables for the Start and End date.
My dates are defined like this (you can use another format as long as they are saved as Dates):

let vLast4WeekStart = date(WeekStart(Today(),-4),'DD.MM.YYYY');
let vLast4WeekEnd = date(WeekEnd(Today(),-1),'DD.MM.YYYY');

Get Variables from the App in sequence:

Step 2. 

Create a list of dates between the start and end date.
I did this with a simple custom nodejs script.

After getting the Variables, apply a custom code

2.1
The script has the start and end date as inputs  in the format of a UNIX Timestamp, divided by 86400 (to get the day-scope instead of seconds).

{divide: {date: {$.getVariable.qDefinition},'U'}, 86400}

Custom Code Settings:

2.2. 

• In the Script, cast the date Values to Numbers (to be sure they are interpreted correctly) and save them in variables to be used in the script.
• Then save the difference between the two values to a new variable diffD to get the number of dates between the start end end Date. Don't forget to add +1 to include the end date.
• Loop through all the days, starting from the startdate until the end date is reached (the number of loops is defined by the difference in days defined in diffD). 
• Increment the Date by +1 for each loop.
• For each loop, push the current date to an array.
• And here is the IMPORTANT part, that cost me a lot of time and nerves, since I did not realize this: 
  Add 25569 to each date, because Qlik calculates the date values by counting from 01.01.1970 (same as Excel) and not counting from 01.01.1900 as the Unix-ts. 25569 is the difference in days between these two dates, so you must add these!
• Log the array in the console, so it can be used in later automation modules

//Inputs from Variables.
//They have to be formatted as an Unix Timestamp devided by 86400 (to get the Date instead of seconds)
const startD = Number(inputs['dateStart']);
const endD = Number(inputs['dateEnd']);

//Calculate difference in days between the two dates
const diffD = endD - startD +1;

//Set dynamic variables for the loop
let currD = startD;
let dateList = [];

//loop from Start date until the end date and push to an Array
//Add 25569 to each date value (the difference in days between 01.01.1900 and 01.01.1970) in order to get the correct numerical date values
for (let i = 0; i < diffD; i++){
  dateList.push(currD+25569);
  currD++;
}

console.log(dateList);

2.3.
Add the custom code as a Raw input to the values selection of your Date-Field

And that is it. I repeat this flow 3 times, for each Alternate State.

I will probably use this workflow as a template for all exports I create with the Qlik Reporting Connector, since our Apps usually have to be filtered to specific timeframes.

Full Sequence as reference:

Also special thanks to @Emile_Koslowski  since the details you provided in this thread , especially your last remark to the other user to check how dates from the date Field are saved as a Raw Input, helped a lot.
Before that, I was trying to apply the dates in a text representation, which strangely worked for one date, but not an array of dates.

[Ldi]

Hello, thanks for your solution !

I've adapted it in php and without the get variables :

// Date de début et de fin
$debut = new \DateTime('30 days ago');
$fin = new \DateTime();
 
// Ne pas toucher le code ci-dessous :
//Transformation des dates au format Qlik
$start = (int)($debut->getTimestamp()/86400+25569);
$end = (int)($fin->getTimestamp()/86400+25569);

//Tableaux des dates à filtrer
$tabDates = [];
for($i = $start; $i<= $end; $i++) {
	$tabDates[] = $i;
}
//Output du tableau
echo json_encode($tabDates);

 and just add to selection the result of this custom code.

[PhilipZ]

Hey Ldi,

I have a new Account, but I am the OP of this thread.

Thanks for your nice addition.

That is actually a great simplification of my approach for getting the correct date-values.

I like that you do all the transformations in code and might just adapt mine to do the same. I Have not worked much with php yet but that might be just the chance. And otherwise I can just transform it to my JS-Script.

I do like to pull the dates from variables to reflect possible changes in my app in my automation and I do calculations to get the start and end of the week which is pretty simple in the Qlik Data Editor.

However, I do like the possiblity of doing this in Script. I have never seen it the way you do it: e.g. \DateTime('30 days ago'). Is there any documentation for that?

Thanks,
Philip