Average weights calculation using over function

Hi All,

I am using this expression to calculate Average Budget

Sum([C]) over (LastPeriods(4,[Axis.X])) * Avg([Weights]) over (LastPeriods(4,[Axis.X])) / 100000 / 
Sum([C_Weights]) over (LastPeriods(4,[Axis.X]))

I am facing difficulty in calculating Avg(Weights)

When I calculate Avg(Weights) values are matching with my excel file but when i use Avg(weights)over (LastPeriods(4,[Axis.X])) valuse are going wrong,

Instead of giving Avg of each quarter it is giving just average of previous four quarter because of which my entire calculation is going wrong.

Sum([C]) over (LastPeriods(4,[Axis.X])) * Avg([Weights]) over (LastPeriods(4,[Axis.X])) is showing wrong value.

For example:

For China data in Q3 2017 desired value is 0.84 but Spotfire value is 0.85 and the difference is huge for others quarters.

Can someone please help me to alter this expression so that i can get exact values.

I have attached excel file which contains pivots ,calculations and also Spotfire file which has visualization.



Thank You



File test_in_tibco.xlsx164.83 KB
Binary Data trial_-_sheet1.dxp225.79 KB
File global_check.xlsx24.51 KB

(1) Answer