How to compute a correct moving average in Nprinting ?

[Marjo]

Hi there,

I am trying to compute a 5-weeks moving average correctly to display it in a graph in a Nprinting report.

Basically my data looks like the attached example : 

- week id

- Nb is my measure

- Moving average 5 weeks : the 5-week moving average of the measure "Nb"

I want to display "Nb" and its "Moving average 5 weeks" only over the last 10 weeks, so between week 202411 and 202420 in my example.

But for weeks 202411 to 202415, I need older data points to be able to compute properly the 5 weeks rolling average, example : 

Moving average 5 weeks for week 202411 = (120 + 200 + 27 + 128 + 625 )/5 = 220

Problem : if I filter on the last 10 weeks only, I miss older data required to compute properly the 5-weeks rolling average. For 202411, I will only get 120/5 = 24 which is wrong.

How can I do to compute properly the moving average for each point and only then, filter to display only the 10-week period of my interest ?

What I have already tried without success :

- compute the moving average in QlikSense and then filter on 10 last weeks in Nprinting graph : FAILED

- filter on 20 last weeks in Nprinting graph to get correct moving average value and then apply an additional filter on 10 last weeks at task level (hoping the aggregation would be performed before the final filter on period) : FAILED

Thanks for your help

[Lech_Miszkiewicz · Accepted solution]

Hi @Marjo,

Is this the only measure and only chart you want to calculate for your report? Probably not - so as always we need to have more clear picture how the whole report would need to look like, what would be the output, what sort of visualisations you would use.

My method for rolling periods is super easy and is based on planning and creating required data model. In this case it requires linking calendar with necessary flag.

As you can see applying selection on column A as a dimension creates required groupings of calendar weeks. Same thing you would do in your NPritning filter - you would apply it on rolling period column and rest would be maintained via set analysis.

attached is ready to use app and sample GIF

cheers

Lech

[Marjo]

Hi @Lech_Miszkiewicz , thank you for your feedback.

However we don't have any mean to handle this without touching the data model ? I mean, can't we solve it using formulas and or filters on the data we already have ? Because, I need to compute a 5-week rolling average now but what if then I (or someone else using the same data model) also need a 1-year rolling average and a 6-month rolling average or "any other value"-rolling average ? 

With your proposal it works if we have only few rolling average use cases but it does not sound very flexible to cover all possibilities.

Also then it means the "moving average" functionality in QlikSense is just never working as such, except if all people adapt precisely their calendar to their precise use case. This is a bit deceiving.

[Lech_Miszkiewicz]

Hi @Marjo 

I have provided you my opinion about how I handle it. My example shows only 1 period of rolling periods, but you can create as many flags as required in your rolling calendar and use them to your liking. One calendar can handle as many rolling periods as you like. All you need is just additional columns for required periods.

What you are showing in your screenshot is a "wizard" which tries to put together expression and it is catered for the very simple use cases in Qlik Sense. If you look at the actual expression created for such modifier you will see what it does and why it does not work for what you want it to.

Looking at the expression you see that it does not care what you are selecting in your filters/selections so as soon as you start applying filters in your Qlik app it will calculate based on whatever is within current selection and also it will do different calculation if you change sorting of your dimension as the formula is relying on UI table layout and "Above" function in it. 

I will not be able to give you one working formula for your need as for that I would need to see your app and understand how you are going to use it, what filters you will apply etc..

I will add to it that working with NPrinitng (which is template based tool) should require little bit of consistency and planning - hence from my experience i would rather spend time discussing it all with business and planning what they want before building template which I would have to constnantly change. Dealing with data via data model makes NPrinting job much simpler to manage, maintain and troubleshoot. You dont need to create rolling average formulas. You just make Sum(Field)/X. It makes your formulas super easy to understand and you will not scratch your head when trying to troubleshoot things as you will be able to preview all connections in model and results coming from your data. It will work regardles of sorting on your object. It will allow you to filter rolling periods as opposed to regular periods which would break your numbers.

I am not suggesting any other option using formulas for the reasons explained above.

If there is anyone else with better solution I am happy to challenge it but from my quite long experience as a Qlik partner I have never seen better solution for rolling periods in Qlik.

cheers