topic Re: Aggr Count Distinct in QlikView

Aggr Count Distinct

zagzebski — Mon, 02 Sep 2013 17:20:48 GMT

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

Re: Aggr Count Distinct

swuehl — Mon, 02 Sep 2013 17:38:21 GMT

Maybe like attached?

Re: Aggr Count Distinct

zagzebski — Mon, 02 Sep 2013 17:48:14 GMT

Not seeing a file attached?

Re: Aggr Count Distinct

swuehl — Mon, 02 Sep 2013 17:52:22 GMT

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, 3, 150, Meets Criteria

B, 1, 100, Meets Criteria

B, 4, 50, Doesn't Meet Criteria

C, 1, 150, 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

zagzebski — Mon, 02 Sep 2013 18:11:13 GMT

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

zagzebski — Mon, 02 Sep 2013 18:54:13 GMT

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

zagzebski — Mon, 02 Sep 2013 18:57:35 GMT

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

swuehl — Mon, 02 Sep 2013 19:42:53 GMT

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.

Re: Aggr Count Distinct

zagzebski — Mon, 02 Sep 2013 20:02:17 GMT

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

Re: Aggr Count Distinct

zagzebski — Mon, 02 Sep 2013 21:28:06 GMT

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

swuehl — Tue, 03 Sep 2013 08:46:25 GMT

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

zagzebski — Tue, 03 Sep 2013 12:46:51 GMT

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

swuehl — Tue, 03 Sep 2013 12:58:08 GMT

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

zagzebski — Tue, 03 Sep 2013 13:18:09 GMT

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).