how to calculate last 12 month rolling sum properly in Spotfire?
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.