
Re: Aggr Count Distinct
Stefan Wühl Sep 2, 2013 1:38 PM (in response to Steve Zagzebski)Maybe like attached?

comm90893.qvw 135.8 K

Re: Aggr Count Distinct
Steve Zagzebski Sep 2, 2013 1:48 PM (in response to Stefan Wühl )Not seeing a file attached?

Re: Aggr Count Distinct
Steve Zagzebski Sep 2, 2013 2:11 PM (in response to Stefan Wühl )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...


Re: Aggr Count Distinct
Stefan Wühl Sep 2, 2013 1:58 PM (in response to Steve Zagzebski)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)
]

Re: Aggr Count Distinct
Steve Zagzebski Sep 2, 2013 2:54 PM (in response to Stefan Wühl )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 
Re: Re: Aggr Count Distinct
Steve Zagzebski Sep 2, 2013 2:57 PM (in response to Stefan Wühl )
I attached an excel example in case you have the time to look this over. Thanks again for you help!
Re: Re: Re: Aggr Count Distinct
Stefan Wühl Sep 2, 2013 3:42 PM (in response to Steve Zagzebski)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.

comm90893b.qvw 153.2 K

Re: Aggr Count Distinct
Steve Zagzebski Sep 2, 2013 4:02 PM (in response to Stefan Wühl )Again thanks! I know what I want to do but I struggle with the syntax too often!

Re: Aggr Count Distinct
Steve Zagzebski Sep 2, 2013 5:28 PM (in response to Stefan Wühl )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

Re: Aggr Count Distinct
Stefan Wühl Sep 3, 2013 4:46 AM (in response to Steve Zagzebski)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)

Re: Aggr Count Distinct
Steve Zagzebski Sep 3, 2013 8:46 AM (in response to Stefan Wühl )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

Re: Aggr Count Distinct
Stefan Wühl Sep 3, 2013 8:58 AM (in response to Steve Zagzebski)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)

Re: Aggr Count Distinct
Steve Zagzebski Sep 3, 2013 9:18 AM (in response to Stefan Wühl )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).






