sum if ever exists in list box (on selected year)

[felipe_oliveira]

Hi Qlik masters

I need to come out with a expression that sum up if a field ever existed (active) in a list box during the selected year.

Please find further details below:

DWH Table

Key%	Account	Vendor_loc_Code	DWH.Program	DWH.Date	Year	Month	Balance$
1	0001	111	A	01-Jan-2017	2017	Jan	100
1	0002	111	E	01-Jan-2017	2017	Jan	50
2	0003	222	B	01-Jan-2017	2017	Jan	10
3	004	333	F	01-Jan-2017	2017	Jan	30
4	0001	111	A	01-Feb-2017	2017	Feb	120
4	0002	111	E	01-Feb-2017	2017	Feb	90
5	0004	333	F	01-Feb-2017	2017	Feb	30
6	0002	222	B	01-Feb-2017	2017	Feb	110
7	0001	111	A	01-Mar-2017	2017	Mar	160
7	0002	111	E	01-Mar-2017	2017	Mar	90
8	0004	333	F	01-Mar-2017	2017	Mar	85

%Key = AutoNumber(vendor_loc_Code & Date(DWH.Date, 'MMYY'))

Dealer Table

Key%	Application	Dealer Name	Vendor_Code	Dealer.Program	Submitted.Date	DWH.Year	DWH.Month	Submitted Amount$
2	11111	YY	222	B	01-Jan-2017	2017	Jan	100
1	22222	XX	111	A	01-Jan-2017	2017	Jan	20
1	33333	XX	111	E	01-Jan-2017	2017	Jan	40
6	44444	WW	222	B	01-Feb-2017	2017	Feb	40
4	55555	XX	111	A	01-Feb-2017	2017	Feb	40
5	8888	XX	333	A	01-Feb-2017	2017	Feb	55
7	66666	XX	111	A	01-Mar-2017	2017	Mar	30
7	77777	XX	111	E	01-Mar-2017	2017	Mar	15

%Key = AutoNumber(Vendor_Code & Date(Submitted.Date,'MMYY'))

The link between the 2 tables is %Key2

I have the following list tables

Year (from Dealer Table)

Month (from Dealer Table)

Dealer Name (from Dealer Table)

Dealer Program (from Dealer Table)

I select Year = 2017, Month = Feb and Dealer Name = XX

The current expression to calculate the Sum Balance$ is:

Sum({1}{<Year={$(=($(vFieldYear)))}, Month={$(=($(vFieldMonthName)))},DWH.Program = p({1}Dealer.Program)>} NET_BAL)

Straight table actual:

Dealer.Name	DWH.Program	Count Account	Sum Balance$
XX	A	1	120
XX	E	1	90
XX	F	1	30

Straight table expected:

Dealer.Name	DWH.Program	Count Account	Sum Balance$
XX	A	1	120
XX	E	1	90

Expected logic:

1) I would like to disclose the sum balance of the maximum date selected (Year and month). It cannot sum over different months as the balance field is a cumulative field.

2) I would like to show in the DWH.Program column, only programs available in the Dealer table (regardless of the period of the year). For example, in Feb 2017, Program E was not submitted but I would like to have it included as it has balance in the same month on table DWH. Program F should not be disclosed as it does not on Dealer table (even though they share the same Dealer Name).

Thanks

Message was edited by: Felipe Oliveira

[sunny_talwar]

Why would you not see Key% 6 here... it is also in Feb 2017 and DWH.Program = B....

[sunny_talwar · Accepted solution]

May be this

Sum({1<Year={$(=($(vFieldYear)))}, Month={$(=($(vFieldMonthName)))}, DWH.Program= p({1<Dealer.Name = p(Dealer.Name)>} Dealer.Program)>} NET_BAL)

[felipe_oliveira]

Thanks Sunny!

It worked! I just needed to make a small change as below. I didn't know we could use p() inside another p().

sum({1<DWH.Program=p({1<Dealer.Name=p(Dealer.Name)>}Dealer_Program),Dealer.Name=p(Dealer.Name),Year={$(=($(vFieldYear)))}, Month={$(=($(vFieldMonthName)))}>} NET_BAL)