Qlik Sense HR Report at given dates entered by end-user

[jpaviles1]

Dear Qlik Community,

I am trying to get a dynamic HR historical report at given dates entered by end-users.

I have the following fields on Oracle SQL Developer query:

Personnel_Id,  (NUMBER(8,0))

Join_Date, (DATE)

Termination_Date   (DATE) - 31/12/9999 for active employees

When running Oracle SQL query:

WHERE TO_DATE('&REPORT_DATE', 'DD/MM/YYYY') BETWEEN "Join_Date" AND "Termination_Date"

Oracle allows me to enter Report Date:

and the query works fine

How can I get this dynamic date selection working in Qlik Sense?

Qlik Sense script:

LET vReportDate = Date(Date#('31/12/2024', 'DD/MM/YYYY'), 'DD/MM/YYYY');
LET vOracleDate = Date(Date#('$(vReportDate)', 'DD/MM/YYYY'));

SQL query WHERE condition:

WHERE TO_DATE('$(vReportDate)', 'DD/MM/YYYY') BETWEEN "Join_Date" AND "Termination_Date";

I need users entering whatever the date and Qlik Sense reporting that information. It is now statically considering 31/12/2024 as reporting date. How to make 

LET vReportDate = Date(Date#('31/12/2024', 'DD/MM/YYYY'), 'DD/MM/YYYY');

Dynamically updating data? Users would enter whatever the dates, reloading data and getting expected results.

Thank you!

Jose 

** UPDATE **  (Possible Solution)

On Data Load Script:

LET vOracleDate = Date(Date#('$(vReportDate)', 'YYYY-MM-DD'), 'YYYY-MM-DD');

On Qlik Sense Script SQL Query:

HR_DATA:

SELECT 

Personnel_Id,

Join_Date,

Termination_Date 

FROM [ Your HR Data Source Table]

WHERE TO_DATE('$(vOracleDate)', 'YYYY-MM-DD') BETWEEN "Join_Date" AND "Termination_Date";

Entering 2025-01-01 as Report Date and Reloading Data:

Report Date 30/06/2024 + Reload Data

[henrikalmen]

If you want data to reload on user actions, you should check out direct query.