how to calculate last 12 month rolling sum properly in Spotfire?

Hi frds,

I am new for spotfire. Actually we are stuck at 12 month rollng cost.

I have the data like SERVICE YEAR MONTH, IB COUNT,  COST(Excludes TP), LABOR_COST,  

I need to dispaly 12 month rolling cost on bar as well as Ib count on Line  in the comination bar chart. 

For 12 month rolling i need to calculate  (Sum([Excludes Tp]) + sum([LABOR_COST])) / Sum([IB Count])

Based on this value i need to calculate last 12 month sum per each SERVICE YEAR MONTH.

For that with help of sean relly from community i got below logic. its works well. 

(Sum([Excludes Tp]) + sum([LABOR_COST])) / Sum([IB Count]) then Sum([Value]) OVER (LastPeriods(12,[Axis.X])) as [12 Month Rolling Cost],

But the problem is - I think This logic has limitations.  ( Means: If i am selecting 24 months of data by using filters  its giving correct output. else if am selecting 12 months of data its showing wrong values.

But my ultimate goal is - we need to display only last 12 months rolling in bar chart with correct values. 

I hope you understand well. can u pls help me  as much as early posssible. 





(4) Answers