Aggr and Count

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Aggr and Count

sunny_talwar — Wed, 10 Aug 2016 17:03:21 GMT

May be this:

=Sum({<[Category 1] = {'A'}, Group = P({<[Category 2] = {'Blue'}>})>}Dollars)

topic Re: Aggr and Count in QlikView

Aggr and Count

Re: Aggr and Count