Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Sum value dynamically using 2 tables and field value

Hi all,

I have two source tables.

Table1

ProjectPhaseA1A2A3..AxB1..Bx
Initiate104..41..2
Planning241..33..4
Execution132..24..4
Close343..43.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:

KPIAB
17 which is Sum(A1) 11 which is Sum(B1)
211 which is Sum(A2) Sum(B2)
310 which is Sum(A3) Sum(B3)
..Sum(A..)Sum(B..)
x13 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

1 Solution

Accepted Solutions
jyothish8807
Master II
Master II

Hi Giuseppe,

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

Regards

KC

Best Regards,
KC

View solution in original post

8 Replies
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

Not applicable
Author

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
Master II
Master II

Hi Giuseppe,

Hope this is what you are looking for .

Regards

KC

Best Regards,
KC
Not applicable
Author

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
Master II
Master II

This?

Not applicable
Author

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
Master II
Master II

Hi Giuseppe,

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

Regards

KC

Best Regards,
KC
Not applicable
Author

Perfect! Now also the thread has the final version.

Thanks everybody!