topic Re: Set analysis - given a subset of users, count users in QlikView

Set analysis - given a subset of users, count users

Fri, 26 Feb 2016 19:57:23 GMT

I'm looking to write a set analysis that counts a number of users from a subset of users.

Here's the data I'm working with:

MaxScroll only populates if HitType is 'Scroll'

UserId	Page	HitType	MaxScroll
1	www.blah.com/blah1	Scroll	0
2	www.blah.com	Scroll	0.25
2	www.blah.com	Click	-
3	www.blah.com/blah2	Scroll	0.50
4	www.blah.com/blah	Scroll	0.75
4	www.blah.com/blah	Click	-
5	www.blah.com/blah3	Scroll	0.25

I need to count UserId's of users who scrolled more than 0, and who also clicked.

count({<HitType = {Scroll, Click}, MaxScroll -= {0}>}distinct UserId) does not work, since MaxScroll is null in cases of 'Click' HitType, so it returns 0.

Basically I need users who scrolled over 0 (easy =count({<HitType = {Scrolling}, MaxScroll -= {0}>}distinct UserId)) and of those users how many clicked. I'm not sure how to go about doing this.

Thanks in advance.

Re: Set analysis - given a subset of users, count users

swuehl — Fri, 26 Feb 2016 20:38:06 GMT

Maybe like

=Count({<UserId = p({<HitType = {Scrolling}, MaxScroll -= {0}>})*p({<HitType = {Click}>}) >} DISTINCT UserId)

Re: Set analysis - given a subset of users, count users

Mon, 29 Feb 2016 14:32:28 GMT

That worked, thanks!

Would you mind explaining why that worked? I've read about the p() function, but I'm not sure how it's working in this case.

Re: Set analysis - given a subset of users, count users

swuehl — Mon, 29 Feb 2016 20:04:00 GMT

You probably know the white / green / grey colors in QV list boxes.

The p() functions work like retrieving the white colored UserID when you make selections HitType = {Scrolling}, MaxScroll -= {0} resp. HitType = {Click}. So you are retrieving two sets for UserID, then intersect these two sets using the intersection operator *.

Basically it's just translating your requirement

"Basically I need users who scrolled over 0 (easy =count({<HitType = {Scrolling}, MaxScroll -= {0}>}distinct UserId)) and of those users how many clicked. I'm not sure how to go about doing this."

into QV syntax: