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]

Where do the two fields (?) ProductionBeginDate and ProductionEndDate come from? Or are they variables?

In general, you can use an advanced search on your ProdGLDate in your set analysis field modifier, something like (assuming variables):

=sum(

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

               ProdGLDate = {">=$(ProductionBeginDate)<=$(ProductionEndDate)"} >}

Commission)

[zagzebski]

These are not variables - they are part of the load process. I had an example of the expression with variables but can't figure out how to adjust it if these are just regular fields

[swuehl]

If these fields only have 1 distinct value each, you can just replace the variable by the field name:

=sum(

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

               ProdGLDate = {">=ProductionBeginDate<=ProductionEndDate"} >}

Commission)

If you have several values per field, you need to decide which value to pick, maybe the maximum / minimum?

=sum(

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

               ProdGLDate = {">=$(=min(ProductionBeginDate))<=$(=max(ProductionEndDate))"} >}

Commission)

[zagzebski]

Thanks - my problem is I need this variable in a prior load so I was not sure how to handle that. I used these two fields (ProductionBeginDate and ProductionEndDate) as variables in that load then just added them to a table and brought them through to the final model.

Question: what is the best way to get these two variables in a prior QVW and use them in the final model (QVW) too.  I am trying to avoind putting them ni twice (two different files).