5 week Average calculation

[RobMazaheri]

Can anyone help me please I want to calculate the 5 week average of orders I take in.

I have created a Vmax date to show me the weekending date as I have the date I receive my orders but cannot work out how to calculate an average over 5 weeks 

[JonnyPoole]

Is the calculation needed for just one time period? Or do you need to see the rolling average each week for the past 5 weeks?  

For the former I would use set analysis with a max(date) approach,  but for the latter i prefer a data model approach that associates each date with every other date in the prior 5 weeks

[RobMazaheri]

I'm currently using this

COUNT (SPRINT_STATUS) / 5

but I need it to be for the last 5 weeks of data 

[RobMazaheri]

I do have a max date set but dont know how to incorporate this in set analysis 

[JonnyPoole]

If you have a date field called [Date], you can do this via SET ANALYSIS. I like to use variables in the set analysis modifier to make it more readable and stable. 

Count( {<Date={">=$(vMaxDateMinus35)<=$(vMaxDate)"}>} SPRINT_STATUS)

In this example I use two variables as follows:

vMaxDate

=Max(Date)

vMaxDateMinus35: 

=Date( Max(Date) -35)

5 weeks = 35 days and its allowed to do integer arithmetic with date fields to add or subtract days so it looks easy to do it this way. 

The set analysis will grab all records that are associated with dates from the max date to 35 days prior. 

Its hard to see all the values for 5 weeks in the next screenshot but if you focus on the highlighted portion you can see it starts skipping values more than 35 days prior to the max date. 

[RobMazaheri]

[RobMazaheri]

I have this will give it a go in a minute to see if it works 

[RobMazaheri]

[RobMazaheri]

[RobMazaheri]

Doesn't

seem to be calculating correctly