Handling Date Variables in Mail & Deploy Reports

Handling Date Variables in Mail & Deploy Reports

You have a date variable in Qlik (formatted as DDMMYYYY), but when used in Mail & Deploy, it gets converted into a number. For example, the file output appears as File_45014.xlsx instead of File_29032023.xlsx.


Solution:
To resolve this issue, you can modify the expression in Mail & Deploy to ensure the variable retains its date format. Use one of the following expressions in the Expression Field of Mail & Deploy:
  1. If Variable Requires Dollar Expansion in Qlik:
    [Datasource].[TEST APP].[Expression].[text(date($(vS_Date), 'DDMMYYYY'))]  

    Use this if your Qlik variable vS_Date includes dollar-sign expansion (e.g., $(vS_Date)).
  2. If Variable Does Not Require Dollar Expansion:

    [Datasource]    .[TEST APP].[Expression].[text(date(vS_Date, 'DDMMYYYY'))]
    Use this if your Qlik variable can be directly referenced without dollar-sign expansion.

Steps to Implement:
  1. Open the Report Template in Mail & Deploy Designer:
    • Navigate to the section where the file name or variable is being used.
  2. Update the Expression Field:
    • Replace the current reference to the variable with the appropriate expression from above, depending on whether your Qlik variable uses dollar expansion.
  3. Save and Test the Report:
    • Generate the report and verify that the file name now uses the correct date format. For example, File_29032023.xlsx instead of File_45014.xlsx.
Additional Notes:
  • Ensure that the vS_Date variable in Qlik is correctly formatted using the Date() function before being passed to Mail & Deploy.
  • Use the preview mode in Mail & Deploy to test changes without generating a full report.
By applying the correct expression in the Mail & Deploy Designer, you can ensure that date variables retain their intended format and are displayed correctly in your report outputs.