topic Re: Set analysis , P() and E() in QlikView

Set analysis , P() and E()

Tue, 05 Nov 2013 09:13:59 GMT

Dear community,

I have a trouble using set analysis P() and E()

I have one access log table as below:

UserID	URL	ACCESS_DATE	Content_category
id1	/url/request1	2013/11/2	A
id1	/url/resuest2	2013/11/2	B
id2	/url/request3	2013/11/2	A
id3	/url/request4	2013/11/3	C

I want to count UserID who accessed Content_category A and who didn't access Content_category B.

I tried in chart:

dimension: ACCESS_DATE

expression:

count({$ <UserID = P({$<Content_category = {'A'} UserID) - P({$<Content_category = {'B'} UserID)>} )

DISTINCT UserID )

But it doesn't work.

when I select on ACCESS_DATE on list box of ACCESS_DATE, it works well.

But I select over 2 ACCESS_DATE, UserID count is not correct.

What happend in this script ?

Re: Set analysis , P() and E()

swuehl — Tue, 05 Nov 2013 10:02:38 GMT

Set analysis is calculated once per chart, not per dimension value. It won't consider your current dimension value (ACCESS_DATE), so it will select the UserID's that ever accessed A and never accessed B (ever / never with regard to your selections in ACCESS_DATE field).

Does this explain what you see?

Re: Set analysis , P() and E()

Tue, 19 Nov 2013 10:47:13 GMT

Thanks. I understand.

But, then do you know how to calculate what I want?

Re: Set analysis , P() and E()

Tue, 19 Nov 2013 12:08:29 GMT

You could try an If() statement with an aggregation over ACCESS_DATE and UserID:

Count(DISTINCT Aggr(If(Content_category='A' and Content_category <> 'B',UserID),UserID,ACCESS_DATE))

Re: Set analysis , P() and E()

jerrysvensson — Tue, 19 Nov 2013 12:37:17 GMT

Will this work for you?

count({<UserID = E({<Content_category = {'B'}>} UserID), Content_category = {'A'}>} UserID)