topic Re: Sum in excel nprinting in Qlik NPrinting

Sum in excel nprinting

P_qlik — Wed, 31 Mar 2021 10:30:30 GMT

Hi,

I need to make the sum of the values in excel using a table object, but if I put this formula: SUM(B2) where B2 is the cell where there are the values, in the output I have the count of the number of rows, not values

For example I have this numbers: 1,5,7,6, the result is 4 but I need 19

Which formula should I use?

Thank you

Re: Sum in excel nprinting

sk88024 — Wed, 31 Mar 2021 10:46:30 GMT

"SUM(B2) where B2 is the cell where there are the values" - B2 is the single cell but you have mentioned that numbers are like 1, 5, 7, 6, then there should be multiple cells. If my understanding is not correct, can you please elaborate.

Re: Sum in excel nprinting

P_qlik — Wed, 31 Mar 2021 10:54:59 GMT

hi,

yes it's correct, but the values change every month so I need a dynamic formula, today there are 4 values, tomorrow maybe 5, I need something that gives me the correct sum and it's based on the number that changes

Re: Sum in excel nprinting

sk88024 — Wed, 31 Mar 2021 11:44:48 GMT

Can you provide some sample data and qvw file. It may help us in finding the solution.

Re: Sum in excel nprinting

P_qlik — Wed, 31 Mar 2021 12:58:34 GMT

I can send you 2 photoS:

I should work on field quantity and when I click on anteprima (preview), at the end of the excel I need to have the sum of all values, something like this:

thank you

Re: Sum in excel nprinting

Ruggero_Piccoli — Wed, 31 Mar 2021 12:58:40 GMT

Hi,

You can create a native Excel table and add formulas as explained in https://help.qlik.com/en-US/nprinting/February2021/Content/NPrinting/ExcelReports/Make-Calculated-Column.htm.

If you need totals and subtotals you can follow the instruction in https://help.qlik.com/en-US/nprinting/February2021/Content/NPrinting/ExcelReports/Excel-Reports-Nesteds.htm but not that using levels require longer report generation time so I suggest to work with native Excel tables. Note that the formula, for example a sum(), must include two rows so Qlik NPrinting understands that it has to expand it.

Note also that Qlik NPrinting, by default, adds as many rows as it needs when creating Excel reports. This can change cells references and it can be modified by changing the Expand Range property https://help.qlik.com/en-US/nprinting/February2021/Content/NPrinting/ReportsDevelopment/Designer-properties.htm#anchor-6.

Best Regards,

Ruggero