Rolling 3 Month calculation

[simonbowers]

I new to Qlik Sense and Coding.  I figured out the basis but I'm stuck on a rolling 3 month calculation.

I want to show on a line graph with Month as the dimension the average experience based on rolling 3 month period split by area.

The month dimension has been created with a master calendar

The various threads I've read keeping taking me back to functionality to do this in Qlik View, which I do not have access to. 

I can't share the raw data due to confidentiality. In my data is a column for the experience score, the date of experience and area of experience.

Looking through various threads I thought an As of Month calendar would help but none of the threads I reviewed so far explain how to create these in terms as a complete beginner I understand.

Can anyone help?

[paola_valenti]

Try to create your line graph with:

DIMENSION: MONTH, AREA

MEASURE:

=sum({<DATE={">=$(=MonthStart(AddMonths(today(),-2))) <=$(=MonthEnd(today()))"}>}VALUE)

I hope I correctly understand your question.

[narband2778]

Hi,

Try this

LET vLast3FullMonths = 'MonthYear=,CalDate={">=' & num(floor(monthstart(today(),-3)))& '<= ' & num(floor(monthend(today(),-1)))& '"}';

Thanks,

Naresh

[balabhaskarqlik]

At least can you post, what are expressions you are using in chart?

Then, it's possible to give better idea on it.

[simonbowers]

Thank you for the suggestions so far.

Bala Bhaskar - thank you for your reply. I'm seeking to learn what expression I should be using in the chart.

It's a combo chart that I'm using.

For test purposes I have worked out what the values should be using excel but I've been unable to match these on Qlik Sense.

I know if plotting month on month the expression is Avg({<Area={'Activity1'}>}Experience), this is then done for each area with the Activity referenced.

For my Master Calendar I used the guide produced by Joss Good.

[simonbowers · Accepted solution]

This worked:

Num(Rangesum(Above(sum({<Area={'Activity1'}>}Experience),0,3))

/

Rangesum(Above(Count({<Area={'Activity1'}>}Experience),0,3)))

Not sure why can't use an Avg but it works so I'm happy. Thank you to the people who replied.