topic Re: Subtracting values from two different pivot tables with different dimensions in QlikView

Subtracting values from two different pivot tables with different dimensions

Anonymous — Wed, 05 Sep 2018 20:35:27 GMT

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

Re: Subtracting values from two different pivot tables with different dimensions

Anonymous — Wed, 05 Sep 2018 20:36:43 GMT

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

Oops

Re: Subtracting values from two different pivot tables with different dimensions

marcus_sommer — Thu, 06 Sep 2018 12:30:43 GMT

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

Re: Subtracting values from two different pivot tables with different dimensions

Anonymous — Thu, 06 Sep 2018 18:48:01 GMT

I see, thank you for your explanation Marcus.

A.R