Matrix calculations

[JMIltner97]

I am trying the following:

I have a Table containing transactional data and I want to create a Matrix, which shows the Dates as the Rows and the sum of sales for each country in the columns. So far so good, as thats not too complicated.

Now I however would want to show a last column containing the sum of sales (for all countries combined) for the exact date but 1 year prior.

I tried writing formulas with set analysis but struggle as they are not evaluated at a row context.

How can I achieve this? I am super happy for any tips and tricks on hwo to achieve this?

Best,

Jan

[Padma123]

Try it once..

in script

table1:

load *,AddYears(date,-1) as previousyear;

table2:

load country,Sum(sales) as [each country sales]
Resident table1
Group by country;

in pivot table

row dimension-date

column dimension-country

measure-sum([each country sales])

measure2-Sum(total{<country=p({<previous>})>}sales)