Null values / Band / assign a filter to a row

[newqlik2017]

I got the below data sample and attached is the QVW for reference. I'm trying to associate a filter column to the ID. The idea is when an user makes a filter selection, the associated data is returned. I got two issues:

1) On 'Raw Data' chart, selecting filters STAT = AB, UNIT = EDUCATION, STATE = CA, COUNTY = SAN_DIEGO, CITY= VISTA

should result in two rows - one for each zip, so the total amount should be 100 (40 + 60) not 100.

2) On 'OUTPUT' chart, I want to assign the amount based on the user filter selection to be assigned to the correct band.

Please help on what am I missing.

ID, DATE, UNIT, STAT, STATE, COUNTY, CITY, ZIP, AMOUNT
S1, 201709, EDUCATION, NULL, NULL, NULL, NULL, NULL, 1000
S2, 201709, DEFENSE, NULL, NULL, NULL, NULL, NULL, 2000

S3, 201709, EDUCATION, AB, NULL, NULL, NULL, NULL, 400
S4, 201709, EDUCATION, XY, NULL, NULL, NULL, NULL, 600

S5, 201709, DEFENSE, AB, NULL, NULL, NULL, NULL, 1200
S6, 201709, DEFENSE, XY, NULL, NULL, NULL, NULL, 800

S7, 201709, EDUCATION, AB, CA, NULL, NULL, NULL, 400
S8, 201709, EDUCATION, AB, NY, NULL, NULL, NULL, 600

S9, 201709, EDUCATION, AB, CA, SAN_DIEGO, NULL, NULL, 300
S10,201709, EDUCATION, AB, CA, SFO, SFO, NULL, 100
S11,201709, EDUCATION, AB, NY, SUFFOLK, NULL, NULL, 600

S12,201709, EDUCATION, AB, CA, SAN_DIEGO, SAN_DIEGO, NULL, 200
S13,201709, EDUCATION, AB, CA, SAN_DIEGO, VISTA, NULL, 100
S14,201709, EDUCATION, AB, CA, SFO, SFO, NULL, 100

S15,201709, EDUCATION, AB, CA, SAN_DIEGO, SAN_DIEGO, 90123, 120
S16,201709, EDUCATION, AB, CA, SAN_DIEGO, SAN_DIEGO, 90125, 80
S17,201709, EDUCATION, AB, CA, SAN_DIEGO, VISTA, 91234, 40
S18,201709, EDUCATION, AB, CA, SAN_DIEGO, VISTA, 91235, 60
S19,201709, EDUCATION, AB, CA, SFO, SFO, 90001, 100

[newqlik2017]

Yes, u were correct. The solution you provided works. I had to restructure the data to get the correct results.

Thank you.

[sunny_talwar]

Super