How to calculate a column based on a previous value

[Tanase Andrei]

I have a table created in spotfire, please see bellow:

 

 

 

 

 

MTD

YTD

 

 

country

territory

Actual

Budget

Forecast

Y-1

Actual

Budget

Forecast

Y-1

 

 

America

total

17.65%

14.75%

15.45%

13.94%

8.70%

12.23%

20.60%

15.20%

 

 

Italy

total

17.78%

14.56%

15.20%

26.70%

20.37%

20.60%

15.20%

15.45%

 

 

LATAM

Brazil

15.30%

12.23%

16.45%

19.80%

16.73%

15.20%

14.88%

15.20%

 

 

 

Colombia

19.20%

20.60%

11.50%

22.56%

14.97%

15.45%

17.23%

16.45%

 

 

 

Mexico

15.55%

15.20%

20.78%

15.48%

24.55%

15.20%

23.41%

15.20%

 

 

Spain

total

12.88%

14.88%

13.00%

25.23%

22.66%

16.45%

15.20%

17.78%

 

 

Georgia

total

21.50%

17.23%

23.50%

15.49%

27.50%

12.88%

16.45%

15.30%

 

 

Rest of the world

Pakistan

23.98%

23.41%

23.50%

20.67%

14.88%

21.50%

11.50%

19.20%

 

 

 

Armenia

11.10%

10.10%

12.77%

19.91%

17.23%

23.98%

20.78%

15.55%

 

 

 

On the Horizontal axis i have Period (MTD and Year) and Scenario (Actual, Budget,...)

On the Vertical axis i have Country and Territory.

On the Cell Values i have a formula Sum([x]) / Sum([y])

And i need to calculate the following values:

MTD:

1 - for the Actual value, we need to have only the formula "Sum([x]) / Sum([y])", which is it now, but it's appliedfor all the columns, and it shouldn't

2- for the Budget value, we need to have Actual column - Budget column, and we need to show the difference

3- for the Forecast value, we need to have Actual column - Forecast column, and we need to show the difference

4- for the Y-1 value, we need to have Actual column(which is for current year, eg. 2021) - Actual column(which is for the previous year, eg. 2020), and we need to show the diff.

YTD:

1,2,3,4 - same formulas as above

[Rahul Kumar 8]

for the Y-1 value, we need to have Actual column(which is for current year, eg. 2021) - Actual column(which is for the previous year, eg. 2020), and we need to show the diff.

 

 

Ans:-Sum([Actual column])-Sum([Actual column]) over (PreviousPeriod([Axis.X]))