topic Re: Sum value dynamically using 2 tables and field value in QlikView

Sum value dynamically using 2 tables and field value

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Sum value dynamically using 2 tables and field value

sunny_talwar — Sat, 03 Sep 2016 17:29:05 GMT

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

Re: Sum value dynamically using 2 tables and field value

Sun, 04 Sep 2016 11:48:08 GMT

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.

Re: Sum value dynamically using 2 tables and field value

jyothish8807 — Sun, 04 Sep 2016 12:42:29 GMT

Hi Giuseppe,

Hope this is what you are looking for .

Regards

Re: Sum value dynamically using 2 tables and field value

Sun, 04 Sep 2016 15:22:45 GMT

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.

Re: Sum value dynamically using 2 tables and field value

ecolomer — Sun, 04 Sep 2016 19:23:24 GMT

This?

Re: Sum value dynamically using 2 tables and field value

Mon, 05 Sep 2016 07:39:10 GMT

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!!

Re: Sum value dynamically using 2 tables and field value

jyothish8807 — Mon, 05 Sep 2016 11:34:21 GMT

Hi Giuseppe,

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

Regards

Re: Sum value dynamically using 2 tables and field value

Mon, 05 Sep 2016 14:06:28 GMT

Perfect! Now also the thread has the final version.

Thanks everybody!