I am using the rolling average formula but it is not working on a line chart with dates on the x-axis unless I change the date format to "Week" - but then it show numbers 1-52 on the x-axis instead of the actual dates. Please assist!

[Chris Radvansky]

 

Sum([total_cost]) THEN Avg([Value]) OVER (LastPeriods(4,[Axis.X])) THEN If(Count() OVER (LastPeriods(4,[Axis.X]))=4,[Value],null) as [Rolling Average]

 

[Astrid Blee]

Hi Chris,

The Rolling Average expression in Spotfire requires a categorical axis to calculate over, and cannot work over a continuous one. Therefore, you need to set the date as a non-continuous form, e.g. either in the form Year >> Month >> Date etc., or in the Year / Month / Week forms.

Selecting "Date" from the top of the list (as shown in the below screenshot) uses date as a continuous variable, and therefore won't allow you to use the Rolling Average.

 

When using the Year / Month / Week forms (as given in the second section of the above screenshot), it labels them as the number, where e.g. Week returns every week of the year as an integer between 1 and 54. You could create a data function to return each week as the date of the first day, this is detailed in other Community questions (here for example).

Hope this helps clear up the confusion!

Astrid