How to calculate moving Sum/Averages when using OVER keyword on Next Period.

How to calculate moving Sum/Averages when using OVER keyword on Next Period.

With the current functions available in TIBCO Spotfire we can calculate moving averages or Sum using the OVER keyword along with the LastPeriods() function. However, by using the LastPeriods() function we can only calculate moving averages or Sum over the previous dates itself and not the Next period/days.

For example:
You have a sample data as shown in the image below:


You can find the data where you need to calculate the Sum for the Last 3 days OVER the Date column, and Sum for the Next 3 days OVER the Date column. As, we cannot calculate the Sum/Average OVER the date column for next period in a straight forward way, we need to add a calculated column as 

[Date2]: DateAdd(Date(2017,5,31),DateDiff(Date(2017,5,1),[Date]))

The static date values specified in the custom expression does not affect the final Sum values, you can specify any valid date values at those place and your [DATE] column in the DateDiff() function.

We perform the Sum for last periods(Sum for last 3 days) using the expression in the calculated column as:

Sum for LAST 3 Days:

Sum([Number]) OVER (LastPeriods(3,[Date]))

here, [Date] is the actual date column in your data

However as a workaround, when performing the Sum for next periods(Sum for NEXT 3 days) we need to use the custom expression in the calculated column as:

Sum for NEXT 3 days:

Sum([Number]) OVER (LastPeriods(3,[Date2]))

here, [Date2] is not the same date column(imported), though it will perform the Sum for the next periods over the date.

We could say that, we are trying to use the LastPeriods with a "-" day parameter or trying to evaluate next period.