Subtracting values from two different pivot tables with different dimensions

[Anonymous]

Hi,

I currently have two pivot tables, let's call them Table 1 and Table 2.

Table 1 has two dimensions, Year and Age

Expression = rangesum(before(count(DISTINCT[SKU]),0,AGE))

Table 1

Year\Age      1 2 3

              16|  0 0 1

              17|  0 2 2

              18|  1 4 2

Table 2 also has two dimensions, Year and Lag. Note how here I am using a different second dimension (Lag instead of Age)

Expression = rangesum(before(sum({<Balance={"<=0"}>}aggr(rangesum(above(count(DISTINCT[SKU]),0,LAG)),[SKU],[Year])),0, LAG))

Table 2

Year\Lag      1 2 3

              16|  1 2 3

              17|  2 1 4

              18|  0 2 1

What I would like is a third table which is just the difference of table 2 and table 1. This is very easy to do in Excel, the difficulty that I have here is that both pivot tables have a different second dimension.

What I want

Table 3 = Table 2 - Table 1

Year\Lag      1 2 3

              16|  1 1 4

              17|  1 0 5

              18|  1 2 1

Here is an example of what the data set might look like

SKU	Balance	Year	Lag	Age
123	0	16	3	3
124	-5	17	3	1
124	0	17	3	3
128	-10	17	2	1
128	10	17	2	1
128	20	17	2	2
145	-4	17	2	1
31	22	18	1	1
154	10	18	1	1
12	99	18	1	1
31	-8	18	1	1

Any advice would be greatly appreciated,

A.R

[Anonymous]

I made a mistake when I was calculating the difference in the third pivot table, but hopefully the idea was clear.

Oops

[marcus_sommer]

It's in general not possible to access the results of an object within another object because there is no table created on which another object could access else it's just a temporary calculated view. This means you will need to calculate everything within a single object or you creates appropriate tables within the script.

- Marcus

[Anonymous]

I see, thank you for your explanation Marcus.

A.R