Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

rbpeterson2
New Contributor III

Summation of two sets of Data based on variable

I have two sets of data:

1) Static Table loaded through editor

2) Table loaded through editor and used for selection purposes

On my sheet, I would like to show a bar chart (bar chart 1; shown as percentages) depicting the historical data elements (table 1) and then a separate bar chart (bar chart 2; shown as numeric values) depicting the historical elements (table 1) along with the summation of the selected items (table 2) to the historical data.  The historical data is controlled from the selection of a dropdown variable using the qsVariable (found in Qlik Branch). 

Example table 1:

Name
ValueDollars
Name 1Value 1100
Name 1Value 2300
Name 2Value 1500
Name 2Value 2700

Example table 2 (after selections):

ValueUPB
Value 110
Value 150
Value 140

User selects Name 1 from the dropdown; bar chart 1 responds with showing of the measure Type with the values correctly. (Example would show Type 1 @ 25%, Type 2 @ 75%)

Bar chart 2, however, adds the total of the selections across each type instead of the matching type.  (Example shows: Type 1 historical at 100 and New at 200 (the sum of Type 1 from Table 1 (100) plus Type 1 from the selection (100)) but also shows Type 2 of historical of 300 and New at 400 (it's pulling the sum of the selection into Type 2 even though it's only Type 1 data).

Screenshot:

Left Side is Historical / Right is combination of Historical & Selected+Historical

Right side should only be showing increase in Value 1.

2016-06-07_154658.png

For my "With Selection" expression, I'm using:

(Sum({<Institution={'$(vBankName)'},[Loan Type Flag 1]=>}[Dollar of Loans]) + Sum(UPB))/1000

vBankName is my variable selected in the dropdown that ties to the Institution.

In my example information/data:

Institution = Name

Loan Type Flag 1 = Value

Dollar of Loans = Value

UPB = UPB

As you can see, it's taking the selected UPB dollars and add it across all measures rather than the singular value 1.  How do I get it so it only adds to Value 1?

2 Replies
MVP
MVP

Re: Summation of two sets of Data based on variable

Sorry, Rob,

I tried hard, but I can't follow your description (the low resolution screen shot and the renaming of sample fields between description and expression doesn't help )

Could you post a small sample QVF?

Highlighted
rbpeterson2
New Contributor III

Re: Summation of two sets of Data based on variable

unfortunately i can't post any of the real data as it is confidential and sensitive ... but i can try to recreate a sample set of information