Search code examples
spotfire

Spotfire: moving avg calculation for single column , grouped by an different values of another column

I need help in getting moving average values grouped by specific column. The default moving avg value calculates based on the interval and wont group by any other column. Hence I tried using something like below:

Avg (Column C ) over (LastPeriods(6,Column B)), this gives continuous avg , but wont group on the another column . I need moving avg separately for different set of values grouped by a specified column.

I tried something like (Avg (Column C ) over (LastPeriods(6,Column B), column A)). But this is not correct.

Any help appreciated.

Below is the sample data . enter image description here

I need moving Avg Grouping Column A ie for 170319 the moving avg should be calculated separately and for 170318 separately and 170320 etc...... Remaining columns like Column D and E will have some values which is not required

Solution

  • Your question doesn't clarify if you want the last 6 days, 6 rows, or what ever but here are some options:

    Moving average, sorted on the time, grouped by Column A

    Avg([Column B]) over (Intersect([Column A],AllPrevious([Column B])))

    Moving average over the previous 6 rows (based on Column B ascending)

    Insert calculated column and name it GroupedRowID. This applies a row number based on the grouping of Column A, sorted on Column B ascending.

    Rank([Column B],"asc",[Column A])` as[GroupedRowID]

    Then use this for the moving average

    Avg([Column B]) over (Intersect([Column A],LastPeriods(6,[GroupedRowID])))

    This should get you going...