Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
LLing
Creator
Creator

Past 3 Months Rolling Average in Pivot Table to Ignore Date Filters

Hi,

I have a pivot table that looks like this:

Screenshot (646).png

 

When date filters such as the Month is selected, the column for Past 3 Months Rolling Average displays either - or a wrong value. 

When sep 2021 is selected, I am getting -.

Screenshot (647).png

 

When sept and oct 2021 is selected, I am getting a wrong value.

Screenshot (648).png

 

Dimension rows: MonthYear

Dimension columns: Category and SubCategory

Expression used: RangeAvg(Above(Column(1),1,3))

Past 3 months rolling average ignores the current month and takes into account the previous 3 months. Eg: Nov 2021 uses Aug 2021, Sep 2021 and Oct 2021 data to compute.

I also tried replacing Column(1) with the expression for average price, but it doesn't work.

I want to get the the values in the first table above even after filtering by any date field such as month, quarter, year etc. Can someone help me please.

Thank you.

Labels (5)
8 Replies
vinieme12
Champion II
Champion II

Don't refer the column instead use the full expression and ignore Month selection in set analysis

For example; if your expression for average is = Avg(Values)

Then

Aggr(RangeAvg(Above(Avg({<Month=>}Values),1,3)),Month)

LLing
Creator
Creator
Author

Hi @vinieme12 ,

Thank you for helping me out.

I used RangeAvg(Above(Avg({<Month=>}Values),1,3)) and was able to get the values I want. Aggr by month was excluded as everything appears as - when I added it in, not sure if I'm doing something wrong here.

However when I filter oct 2021, I am getting back all the rows. 

Screenshot (650).png

Is it possible to just show the row Oct 2021? I do not want the rows with - in average price column to show.

vinieme12
Champion II
Champion II

You need to wrap the below expression in aggr and aggr based on the dimensions used in the chart.  

Aggr(RangeAvg(Above(Avg({<Month=>}Values),1,3)), dimensionname1, dimensionname2)

 

 

 

 

 

LLing
Creator
Creator
Author

Hi @vinieme12 ,

After aggr, the rolling averages only appears if I expand to the sub category dimension. How can I also make the rolling averages appear in the category dimension? 

vinieme12
Champion II
Champion II

post a sample

LLing
Creator
Creator
Author

Dear @vinieme12 ,

The column on 3 months rolling is empty before category is fully expanded to show the sub categories.

Screenshot (667).png

 

Upon expanding, then the 3 months rolling values are shown for each sub category.

Screenshot (668).png

Is there a work around to display the 3 months rolling values for both the categories (when pivot table is not expanded) and sub categories (when pivot table is expanded)? Or do I need to present the data using 2 straight tables?

Thank you very much.

LLing
Creator
Creator
Author

Any suggestions please? Thank you.

vinieme12
Champion II
Champion II

Please post a sample