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
QlikWorld online is next week! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
LLing
Creator
Creator

3 Months Moving Average for Average Price Per Unit

Hi,

I have data with fields Date, Material, Amount and Quantity. I want to create a straight table that looks like this. Screenshot (593).png

 Average price per unit is computed by taking sum(Amount)/Sum(Quantity).

3 months moving average contains the max month and the 2 previous months. Example: If max month is 11/21, then months to compute are 9/21, 10/21 and 11/21.

I am facing difficulty aggregating by months for the date field and getting it to work. 

Any suggestions please. Thank you .

Labels (4)
3 Replies
Andrea_Spinetti
Support
Support

Hi @LLing , if I got your request right, I would suggest you create a Master Calendar.

 

Creating a Master Calendar will definitely help the dates aggregation as you will have a complete list of all the dates in the calendar range, avoiding multiplication of the same data in the fact table and making it possible to use the same Calendar for many fact dates.

 

Have a look here: Creating A Master Calendar

 

The video shows the process of creating a Master Calendar in QlikView but the structure will be the same in QlikSense.

If the issue is solved please mark the answer with Accept as Solution.
LLing
Creator
Creator
Author

Hi @Andrea_Spinetti ,

Thanks for helping. I do have a master calendar in my model.

My main issue is I don't know how to write the set analysis expression for the column to display the 3 months moving average based on the max month and  2 previous months. Any suggestion please.

Andrea_Spinetti
Support
Support

Hello @LLing, are you able to share a sample App with dummy data so I can test directly with it?

If the issue is solved please mark the answer with Accept as Solution.