Date format not working in the "add selection to report" block

[ASC-Mike]

Hello Qlik Community,  I can't get dates to filter properly when using the add selection block. I use a field called StopDate.autoCalendar.Date, and use the LOOKUP function and select a single date 9/26/2024 (45561) from the drop down of all of the values returned, it works perfectly and runs the automation for that date. When I use the ADD FORMULA and use the date as Now - 1 day (I want the report for today -1) and use the output format as          m-d-Y it does not work at all, it ignores the date completely. I've spent 4 hours trying every type of date format per the documentation and nothing works.   Any help is greatly appreciated.

Thanks/Mike

[MatheusC]

Hi, @ASC-Mike 
So far I think it is not possible to pass on such a function, due to some limitations due to how these formats are interpreted by Qlik.

If this has any alternative/solution, I'm also interested in knowing.

- Regards, Matheus

[ASC-Mike]

Hello Matheus,

Qlik said it is not yet supported in the add formula.  So they said to create a custom code block to manipulate the date and then allow the selection block to use the output of the custom code block.  It worked.   Here is the basic code for the Custom Code block:

Select language: PHP

Inputs:  call it "date" or whatever you'd like

Value = {date: 'now - 1 day'}   // this is for yesterday, set it to what you want or manipulate in the custom code.

Code: 

$original_date = $inputs['date'];
$str_date = strtotime($original_date);

// Divide by 86400 to get the date part in days, and then cast it to an integer
$formatted_date = intval(25569 + floor($str_date / 86400));

echo json_encode($formatted_date);

// this puts the date in the numeric format that Qlik is looking for

In the Add Selection to Report block:

Field Name:  YourDate.Autocalendar.Date // from your table

Values:  Select custom code block

[MatheusC]

Thanks for sharing.

Good to know that we have this alternative, I will also test this solution here.

[AfeefaTk]

Hi @ASC-Mike 

@MatheusC 

This can be done using the formula as explained in the help page.

https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_QlikAutomation/working-with-... - Section: Convert to Qlik date format
Please give it a try and let me know the feedback.

Thanks