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
For band, Any total amount >= 0 and <= 50 should be in 0-50 band Any total amount >50 and <= 100 should be in 50-100 band
Any total amount >100 and <= 200 should be in 100-200 band
Any total amount >200 and <= 400 should be in 200-400 band
Any total amount >400 and <= 10000 should be in 400-10000 band
For instances, applying filters:
1) City = San Diego, should be reflected on 100-200 band as San Diego has two zip with 120 & 80$ (total 200) 2) State = CA, County = San Diego should be reflected on two different bands: 50-100 => 100 (City VISTA has two zip; 40 & 60, so total is 100$) 100-200 => 200 (City San Diego has two zip: 120 & 80, so total is 200$)
In the above output table example you highlighted, yes that's not correct.
I guess if we can somehow exclude all the rows with NULL we might get the correct total. Also, looks like the Amount for San Diego City and Vista is doubled. Again, it appears because of NULL values for City and Zip.
The total amount for San Diego City is 200 and Vista is 100.
Is this not possible to accomplish in conjunction with the cases (2-6) I listed above?