Nprinting Report: creating a 28-day rolling average trendline in excel

[daveatkins]

I have followed the instructions here to create an excel chart as part of an nprinting (May 2021 SR2) report, creating a data table on one tab and then building charts and slicers on another tab in Excel.

https://help.qlik.com/en-US/nprinting/May2022/Content/NPrinting/ExcelReports/Create-Excel-charts.htm

The requirement for the end users is to email an excel spreadsheet because they are not allowed to have access to our internal QlikSense applications; I am essentially reproducing Qlik features in Excel...

I have been able to add slicers and a linear trendline, but also need to have a 28-day rolling average on the same chart. When I edit the chart in the nprinting designer, I was able to add a 2-day moving average, but I cannot change that to a 28-day because the number in the box will not save as anything but "2".

I suspect this is because in the template, there are only 2 data rows. Anyone solved this kind of issue before?

[Frank_S]

Hi there,

Your post has been moved to the NPrinting Community so it will have more eyes on it from the many NPrinting Developers in that community.
Kind regards...

[Frank_S]

Hi again,

Perhaps you may prefer to use the formula editor achieve your objective?

If your formula works in Qlik Sense, it should also work in NPrinting. (as long as your formula does not attempt to produce an array of values).

Feel free to check the following article at your convenience

• https://help.qlik.com/en-US/nprinting/May2022/Content/NPrinting/ReportsDevelopment/Formulas.htm#anch...
• You cannot add an Aggr() formula, for example Aggr(Count(DISTINCT CustomerID), Salesman), because it returns multiple values. You can resolve this by concatenating the result of the Aggr(), for example concat(Aggr(Count(DISTINCT CustomerID), Salesman), ' - ')

Kind regards...

[daveatkins]

thanks for moving this...I was not able to find nprinting as a forum area when I posted...

I do not understand your response...I need to provide an excel chart containing a linear and a 28-day moving average trendline. The chart is based on a table which is linked to multiple slicers. I get the table from QlikSense and generate the graph in excel, in the Nprinting Designer, so that the user can click on the slicers to interactively change the graph in excel. I am very close now, but I cannot set the moving average to 28 days:

once I preview the chart, I am able to edit the format of the trendline to achieve the desired goal:

preview (both trendlines are faintly visible as dotted lines):

desired (edited the format of the trendline changing #days and color)

[Frank_S]

@rugger_piccoli @lech

Thoughts?

[Ruggero_Piccoli]

Hi,

You could try to create a new, calculated, column in the Excel native table you are using as data source for the chart and make there the rolling average calculation.

About the issue with the chart I suggest that you open a support ticket with everything we need to replicate so see can better investigate.

Best REgards,

Ruggero

[andregas · Accepted solution]

Hello @daveatkins 

I suspect that you cannot set average over than 2 days because the excel support table for graph has only two rows (as you told). You could try to insert at least 28 fake rows in the support table and delete the fake with special NPrinting tag <delete_row>.

Let me know if it solves the problem

Regards

[daveatkins]

this sounded like a great idea, but when I do this...and go to preview:

[daveatkins · Accepted solution]

actually...

by adding the blank rows, I am able to adjust the rolling average to 28. Although a preview/deleterows fails, I can simply delete the rows from the template and the setting of 28 appears to "stick" so when I run the report now...it works!

[andregas]

Hello @daveatkins 

I'm glad to know!

So, you could solve adding the lines, set the trendline to 28 days and finally delete the rows. Does it correct?