Moving Average in Chart with 2 Dimensions

[#1]

Hi all,

I would like show the 1 year moving average of sales per month in a line chart, with one line for each product.

To achieve this, I created a chart with a month dimension and added this formula to it:

RangeAvg(Above(sum([Quantity])+Sum({1}0),0,12)))

This formula works perfectly fine, until I add a second 'product' dimension to the chart for creating separate moving average lines per product. When I do this, the results become completely distorted, which I feel might be because the "Above" function in the formula is not responding to multiple dimensions in the way I had hoped.

Is there any way I can fix this formula, to make it display the accurate moving average lines in a chart with two dimensions?

One alternative I tried was with an "aggr" function to build a temporary table:

sum(aggr(RangeAvg(Above(sum(Quantity])+Sum({1}0),0,12)), [Product], [Month]))

However, this creates problems when used in combination with rangesums (see for my exact issue this post - my data is simply a list of orders and contains null values when aggregated per month, because not every product is ordered every month).

Any suggestions would be appreciated. Thank you in advance!

Stefan

[Gysbert_Wassenaar · Accepted solution]

You can try adding the TOTAL keyword to the above() function so that it can cross the dimension boundaries. Or the AsOf approach described in this document: Calculating rolling n-period totals, averages or other aggregations

[#3]

Thank you for the reply!

I added an AsOf table which seemed to do the trick, because this way I could simplify the expression to just sum(Quantity).

However, there is one small issue remaining, and that is that values are non existent for any 'AsOf table months' in which there have been no sales for 12 consecutive months. As expected, qlik sense simply removes such months from the dimension on the x-axis, rather than displaying them as months with moving average value 0, creating a distortion in the line chart.

I thought this behaviour might be solved through standardising the table by explicitly adding 0 values to it for any month/customer/product combination with no sales, and switching on 'show zero values' in data handling. This however has no effect and the months with a moving average of 0 are still removed from the x-axis. Even with 0 values explicitly added, the chart's behaviour thus seems not to be any different from Null or missing values. Is there any way to fix this?

I'd rather no do this by adding +Sum({1}0) to the expression, because it will cause all products always to be shown in the legend of the graph, even when they're not selected, which is a bit confusing.

Thanks!

Stefan

[Gysbert_Wassenaar]

Can you post a small QS app that illustrates the problem? I'm too lazy to generate a representative data set myself

[#5]

Thanks for the reply! After fiddling around with the data a bit more, I already found out what the problem was though...

Basically the issue was that I had connected this new order table with the 0 values to the original order table via an automatically generated id field with unique values for each product/month/customer combination, but all master tables (calendar, product etc.) were still connected to the original order table. Any filters that I applied therefore first passed though the original order table before getting to the one with the 0 values, which removed all 0 values in the process because these are of course non-existent entries in the original order table. Attaching the master tables to the new order tables with the 0 values solved the issue.

Again many thanks for the help!

Stefan