Hi Qlikview Experts,
Been going at this for hours and can't figure out the right way to do this. Please see table below:
| ID | Group | Dollars | Category 1 | Category 2 |
|---|
| ID001 | ABC | 40 | A | Green |
| ID002 | ABC | 10 | A | Yellow |
| ID003 | ABC | 100 | B | Blue |
| ID004 | DEF | 200 | A | Blue |
| ID005 | GHI | 350 | B | Green |
| ID006 | GHI | 150 | A | Green |
| ID007 | JKL | 500 | B | Blue |
Essentially I need to get the SUM where Category 1 = 'A'. However only include Groups which have at least one line item where Category 2 = 'Blue'.
From the table above, Groups ABC, DEF, and GHI each have records where Category 1='A'. Their values are 40 and 10 (for ABC), 200 (for DEF), and 150 (for GHI), respectively. However only Groups ABC and DEF have at least one line within their respective Group with a Category 2='Blue'. As a result, the output is (40 + 10 + 200 = 250) i.e. 150 from Group='GHI' should be excluded.
I am having trouble properly executing this because I either keep on getting 400 (unsuccessfully excluding the 150 of GHI) or zero.
I have been playing around with applying an AGGR by [Group] on the COUNT of lines where Category 2 = ' Blue' and only including the Dollars in the SUM if the COUNT is more than 0 but it is not working.
Appreciate the help! Thanks!
