Creating Custom Calculations in Excel Reports Using Mail & Deploy?
You want to create an Excel report using Mail & Deploy. Specifically, you have a table spanning columns A to K and need Column G to display the result of Column I × Column B, instead of using the result directly from the Qlik app.
Solution:
While the calculation you are looking for is not directly performed in Qlik, you can achieve it by combining data retrieval with custom Excel formulas.
Steps to Implement the Custom Calculation:
- Retrieve Data from Qlik Sense:
- Use the Mail & Deploy Designer to retrieve the specified columns (A to K) from the Qlik app into your Excel report.
- Ensure that all the required columns, including Column I and Column B, are included in the report data
2. Insert a Custom Formula in Excel:
- In the Excel template, navigate to Column G and insert the formula: =I2 * B2
This assumes that the data starts in Row 2. Adjust the row number as needed. - Drag the formula down for all rows to calculate the desired result dynamically.
3. Configure the Designer to Retain the Formula:
- In the Mail & Deploy Designer, ensure that the formula in Column G is preserved during report generation
- Use the "Keep Cell Formula" setting to avoid overwriting the formula when new data is added.
Tips for Optimal Results:
- If your report contains a large dataset, consider testing the formula in a small sample before generating the full report.
- Use named ranges or dynamic table references in Excel for easier management of formulas.
Related Articles
How to create an Excel report using Snowflake as a data source in Mail & Deploy (with example)?
FIRST CREATED ON 5 Mar 2024 I AUTHOR Emma Camacho Creating an Excel report using Snowflake as a data source in Mail & Deploy allows you to leverage powerful data analysis capabilities. Here's an example to help you get started: Use case: Display ...How to show/hide sheets in Microsoft Excel reports conditionally?
FIRST CREATED ON 19 Dec 2024 I AUTHOR Emma Camacho It is possible to show/hide sheets in Microsoft Excel reports using the Condition property of Elements together with the Exception Behavior. Follow the example to hide Sheets if the CY Sales = 0 and ...What is Report Culture Awareness, and how does it impact Mail & Deploy reports?
FIRST CREATED ON 30 Apr 2020 I AUTHOR Emma Camacho A culture is a set of rules that specify how numbers and dates are formatted (Windows Regional Settings). Generally speaking, the culture defines in what format numbers and dates are being presented ...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 ...How to synchronize cell insertion in an Excel report in Mail & Deploy (with example)?
FIRST CREATED ON 31 Jan 2024 I AUTHOR Emma Camacho Synchronizing cell insertion in an Excel report within Mail & Deploy ensures that your data is accurately aligned and formatted. Here's an example to guide you through the process: If you need a ...