New to Qlik Sense

[LLing]

Hi,

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

 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 .

[Andrea_Spinetti]

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.

[LLing]

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]

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