topic Re: Pivot Table - Sum of Two Fields in QlikView

Pivot Table - Sum of Two Fields

Wed, 25 Nov 2020 16:16:04 GMT

Hello everyone,

I have a pivot table that sums up two different fields using, again, two different fields as dimension. For example:

Year1	Year2	Value1	Value2
1	2	0.455885	0.544115
2	3	0.501606	0.498394
3	4	0.117714	0.882286
4	5	0.866523	0.133477
5	6	0.38556	0.61444
6	7	0.391917	0.608083
7	8	0.542071	0.457929
8	9	0.543696	0.456304

It will be easy to do this via SQL and load in another table that has a shape like this:

Year	Value
1	0.455885
2	1.045721
3	0.616108
4	1.748808
5	0.519037
6	1.006357
7	1.150154
8	1.001625
9	0.456304

However, I am loading in two very large tables already and loading another one would seriously compromise the performance of QV. Is there any way to set up the dimension and expression to achieve this?

My thoughts for the expression will be something like this:

sum(Value1) + sum(Value2)

But for the Calculated Dimension, I'm out of ideas. Hope you guys can help me out.

Thank you!

-Nathan

Re: Pivot Table - Sum of Two Fields

sunny_talwar — Wed, 30 Nov 2016 08:56:31 GMT

I am not sure I understand the logic behind your output table based on your input table. Would you be able to elaborate a little on how did you get from input to output?

Re: Pivot Table - Sum of Two Fields

Wed, 30 Nov 2016 09:10:25 GMT

Hi Sunny,

Apologies for this as I created a sample using excel and the RAND() Function. I copied the wrong input table. So basically this is what happens:

For Year 1 in the output table: We get 0.455885 (Corresponds the the first record since the Year1 field is equal to 1)

For Year 2 in the output table: We get 0.544115 + 0.501606 = 1.045721 (Corresponds to the Year2 field =2 and Year1 field =2)

Hope this clarifies everything.

Re: Pivot Table - Sum of Two Fields

Anil_Babu_Samineni — Wed, 30 Nov 2016 09:13:44 GMT

Do you mean when years are same then you want to do Sum(Value1)+Sum(Value2)

Would you describe more

Re: Pivot Table - Sum of Two Fields

sunny_talwar — Wed, 30 Nov 2016 09:18:40 GMT

May be create an Island table for another field called Year and use that to show your new information on the front end. Please note, since you will be using Aggr() with island table, performance is going to be issue here:

Script:

Table:

LOAD Year1,

Year2,

Value1,

Value2

FROM [https://community.qlik.com/thread/241470]

(html, codepage is 1252, embedded labels, table is @1);

Year:

LOAD MinYear + IterNo() - 1 as Year

While MinYear + IterNo() - 1 <= MaxYear;

LOAD RangeMin(Min(Year1), Min(Year2)) as MinYear,

RangeMax(Max(Year1), Max(Year2)) as MaxYear

Resident Table;

Data model

Table:

Dimension:

Year

Expression:

=RangeSum(Sum(Aggr(If(Year = Year1, Value1), Year, Year1)), Sum(Aggr(If(Year = Year2, Value2), Year, Year2)))

Re: Pivot Table - Sum of Two Fields

Wed, 30 Nov 2016 09:22:36 GMT

To illustrate:

For Year 1 in the output table: We get 0.455885 (Corresponds the the first record since the Year1 field is equal to 1)

For Year 2 in the output table: We get 0.544115 + 0.501606 = 1.045721 (Corresponds to the Year2 field =2 and Year1 field =2) So we sum up Value2 of the first row since Year2 of the first row = 2 and the Value1 of the second row since the Year1 of the second row = 2.