Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
May be this
Sum({1<Year={$(=($(vFieldYear)))}, Month={$(=($(vFieldMonthName)))}, DWH.Program= p({1<Dealer.Name = p(Dealer.Name)>} Dealer.Program)>} NET_BAL)
Why would you not see Key% 6 here... it is also in Feb 2017 and DWH.Program = B....
May be this
Sum({1<Year={$(=($(vFieldYear)))}, Month={$(=($(vFieldMonthName)))}, DWH.Program= p({1<Dealer.Name = p(Dealer.Name)>} Dealer.Program)>} NET_BAL)
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)