Aggr Count Distinct

[zagzebski]

I need to count distinct clients who had a combined 100 in sales. Looking for some help on expressions:\

DATA:
Dept	Client	Sales
A	1	50	Doesn't Meet Criteria
A	1	40	Doesn't Meet Criteria
A	2	75	Meets Criteria
A	2	75	Meets Criteria
A	3	150	Meets Criteria
B	1	100	Meets Criteria
B	1	100	Meets Criteria
B	4	50	Doesn't Meet Criteria
C	1	150	Meets Criteria
C	5	100	Meets Criteria
C	5	100	Meets Criteria
OBJECT:
Dept	Total Sales of Clients who had at least 100 in sales	Count Distinct Clients with Total Sales of 100 or more
A	300	2
B	200	1
C	350	1

[swuehl · Accepted solution]

Maybe like attached?

[zagzebski]

Not seeing a file attached?

[swuehl]

There is one...

Have you looked only your inbox? I think it won't show attachments there. Follow the link to the full discussion.

[edit:

If you can't open it:

I added a field in the script:

LOAD *,

     AutoNumber(Dept&Client) as DeptClient

INLINE [

Dept,    Client,    Sales

A,    1,    50,    Doesn't Meet Criteria

A,    1,    40,    Doesn't Meet Criteria

A,    2,    75,    Meets Criteria

A,    2,    75,    Meets Criteria

A,    3,    150,    Meets Criteria

B,    1,    100,    Meets Criteria

B,    1,    100,    Meets Criteria

B,    4,    50,    Doesn't Meet Criteria

C,    1,    150,    Meets Criteria

C,    5,    100,    Meets Criteria

C,    5,    100,    Meets Criteria

];

then created a chart with dimension Dept and two expressions:

=sum({<DeptClient = {"=sum(Sales)>=100"}>} Sales)

=count({<DeptClient = {"=sum(Sales)>=100"} >} distinct DeptClient)

]

[zagzebski]

Thanks so much - this worked great!!

One question for future reference. It seems like I would have had to use aggregation of some kind to evaluation at finer grain (DeptClient) then the dimension I was using in the object (Dept) but obviously I didn't need to. Is there a general rule of thumb when needing aggregation. I know that is a BIG question...

[zagzebski]

One quick follow up.  I have an extra layer of complexity where I need to take the combinations that fulfiled the criteria for > 100 sales for 2012 and add an expression that ONLY includes those combinations for 2013 Sales and Distinct Counts:

DATA:
Dept	Client	Sales	YEAR
A	1	50	2012	Doesn't Meet Criteria
A	1	40	2012	Doesn't Meet Criteria
A	2	75	2012	Meets Criteria
A	2	75	2012	Meets Criteria
A	3	150	2012	Meets Criteria
B	1	100	2012	Meets Criteria
B	1	100	2012	Meets Criteria
B	4	50	2012	Doesn't Meet Criteria
C	1	150	2012	Meets Criteria
C	5	100	2012	Meets Criteria
C	5	100	2012	Meets Criteria
A	1	100	2013	Doesn't meet criteria because A/1 not valid combination from 2012
A	2	125	2013	Meets criteria because A/2 not valid combination from 2012
A	5	150	2013	Doesn't meet criteria because A/5 not valid combination from 2012
B	2	175	2013	Doesn't meet criteria because B/2 not valid combination from 2012
C	1	200	2013	Meets criteria because C/1 not valid combination from 2012
C	1	225	2013	Meets criteria because C/1 not valid combination from 2012
D	1	250	2013	Doesn't Meet Criteria
OBJECT:
Dept	Total Sales of Clients who had at least 100 in sales	Count Distinct Clients with Total Sales of 100 or more	Total Sales from 2013	Count Distinct from 2013
A	300	2	125	1
B	200	1	0	0
C	350	1	425	1

[zagzebski]

I attached an excel example in case you have the time to look this over. Thanks again for you help!

[swuehl]

There actually is an aggregation grouped by DeptClient:

=sum( {< DeptClient = {"=sum(Sales)>=100"} >} distinct DeptClient)

You were probably thinking of using advanced aggregation (aggr() ) instead, which should also be possible. I find it easier to read and maintain using set analysis.

Adding a set modifier for the YEAR field to the advanced search and the main set expression should solve your second issue.

See attached.

[zagzebski]

Again  -thanks! I know what I want to do but I struggle with the syntax too often!

[zagzebski]

Hopefully this is my last question!

This expression works great:

sum({<^CustDept = {"=[Commission Dept CummPerc]<=$(vRetensionPct)"}>} Commission)

but I need to add the following filter to it:

where ProdGLDate is between ProductionBeginDate and ProductionEndDate