Sum value dynamically using 2 tables and field value

[#1]

Hi all,

I have two source tables.

Table1

ProjectPhase	A1	A2	A3	..	Ax	B1	..	Bx
Initiate	1	0	4	..	4	1	..	2
Planning	2	4	1	..	3	3	..	4
Execution	1	3	2	..	2	4	..	4
Close	3	4	3	..	4	3	.	3

with Ax column and Bx column. This X can increase in the time up to 100 and more values. Max value of A and B is the same: e.g. A30, then also B30.

Table2

KPIs
1
2
3
..
x

with X that can increase up to 100, together with the list of columns at Table1. E.g. Max A is A30, then Max KPI is 30.

I want to create a straight table in Qlikview that put in relation the above two tables in this way:

KPI	A	B
1	7 which is Sum(A1)	11 which is Sum(B1)
2	11 which is Sum(A2)	Sum(B2)
3	10 which is Sum(A3)	Sum(B3)
..	Sum(A..)	Sum(B..)
x	13 which is Sum(Ax)	13 which is Sum(Bx)

I would like to use the value of KPI fields (1,2,3 ..x) in the Sum expression (e.g. Sum(A$KPI) ..but it's not allowed in the expression )

Then, If I select one of the ProjectPhase, the sum will change dynamically. E.g. Select "Execution" and get only A=1 and B=4 for KPI = 1 in the above table.

Any idea?

Thanks

[sunny_talwar]

Not sure I completely understand what you are trying to do. May be trim down the sample with expected output in an Excel might be able to give us better directions

[#3]

I created the table1 and table2 in the attached excel.

Then, the sheet called "qlikview" it's what I want to achieve.

Bear in mind that I'm trying to use some kind of variable or loop counter to make this working even if the number of Ax, Bx and KPI x will increase. There is always a correlation between these X:

If I have 30 KPIs, it means that I have A1 to A30 and B1 to B30. For each KPI (let's say KPI4), I need to sum the value of all A (let's say A4) and B (let's say B4).

In the excel, I created an example with 20 A, 20 B and 20 KPIs.

[jyothish8807]

Hi Giuseppe,

Hope this is what you are looking for .

Regards

KC

[#5]

Hi KC,

that's a really great example. However, this is using a Pivot Table, not a Straight Table. The reason why I need a straight table is because I have some calculation to do on each column (e.g. (A-B)/A) that I cannot do within a Pivot table.

Is there a way to show it in a Straight Table?

Thanks again for your help.

[ecolomer]

This?

[#7]

I found a way to display it in a straight table, using a simple set expression.

=Sum({$<Label={A}>}Data)

=Sum({$<Label={B}>}Data)

Now, I can use a straight table in spite of a Pivot table!

Thanks again for the help!!

[jyothish8807 · Accepted solution]

Hi Giuseppe,

Pfa the updated application. I have added a straight chart also .

Regards

KC

[#9]

Perfect! Now also the thread has the final version.

Thanks everybody!