Summation of two sets of Data based on variable

rbpeterson2 — Wed, 25 Nov 2020 16:16:04 GMT

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	Value	Dollars
Name 1	Value 1	100
Name 1	Value 2	300
Name 2	Value 1	500
Name 2	Value 2	700

Example table 2 (after selections):

Value	UPB
Value 1	10
Value 1	50
Value 1	40

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.

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?

Re: Summation of two sets of Data based on variable

swuehl — Tue, 07 Jun 2016 21:06:17 GMT

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?

Re: Summation of two sets of Data based on variable

rbpeterson2 — Tue, 07 Jun 2016 21:15:04 GMT

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

topic Re: Summation of two sets of Data based on variable in QlikView

Summation of two sets of Data based on variable

Re: Summation of two sets of Data based on variable

Re: Summation of two sets of Data based on variable