Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Discussion Board for collaboration related to QlikView App Development.

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Sum value dynamically using 2 tables and field val...

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Not applicable

2016-09-03
10:10 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Sum value dynamically using 2 tables and field value

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

1,409 Views

1 Solution

Accepted Solutions

jyothish8807

Master II

2016-09-05
07:34 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi Giuseppe,

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

Regards

KC

Best Regards,

KC

KC

1,011 Views

8 Replies

sunny_talwar

MVP

2016-09-03
01:29 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

1,011 Views

Not applicable

2016-09-04
07:48 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

1,011 Views

jyothish8807

Master II

2016-09-04
08:42 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi Giuseppe,

Hope this is what you are looking for .

Regards

KC

Best Regards,

KC

KC

1,011 Views

Not applicable

2016-09-04
11:22 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

1,011 Views

ecolomer

Master II

2016-09-04
03:23 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

This?

1,011 Views

Not applicable

2016-09-05
03:39 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

1,011 Views

jyothish8807

Master II

2016-09-05
07:34 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi Giuseppe,

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

Regards

KC

Best Regards,

KC

KC

1,012 Views

Not applicable

2016-09-05
10:06 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Perfect! Now also the thread has the final version.

Thanks everybody!

1,011 Views