TiBCO Spotfire - How to Calculate only the last 3 columns in a Data - see descr

Question

Week    Sales
1   100
2   250
3   350
4   145
5   987
6   26
7   32
8   156

I wanted to calculate the sales only for the last 3 weeks so the total will be 156+32+26.

If new weeks are added it should automatically calculate only the data from the last 3 rows.

Tried this formula but it is returning an incorrect sum

sum(sales) over (lastperiod(3(week))

https://i.stack.imgur.com/6Y7h7.jpg

Answer 1

If you want only the last 3 weeks sum in calculated column you can use a simple if calculation.

If([week]>(Max([week]) - 3),Sum([sales]),0)

If you need 3 weeks calculation throughout table use below one.

sum([sales]) OVER (LastPeriods(3,[week]))