topic SET Analysis - Baisc Question in QlikView

SET Analysis - Baisc Question

Fri, 23 Sep 2011 17:17:23 GMT

Hello

I was trying to understand set analysis and to see whether it can be used for the following situation

Here is my data

I am trying to identify and report all the accounts who bought DVD in 2007, 2008 , 2009 , 2010 and 2011

The accounts who qualify are A001 and A005 .(A002 would NOT qualify becase ther is no DVD purchase in 2008)

Please see the attached QVW file.

In the first section I was expecting

PRODUCT #Accounts

DVD 2

Can we really use set analysis for the what I am looking for?

Thanks,

A.Paul

SET Analysis - Baisc Question

swuehl — Fri, 23 Sep 2011 17:29:32 GMT

Hi,

yes, I think you are quite close for what you are looking, try this:

COUNT(

{ $<

PRODUCT={DVD},

ACCOUNT= p({<ORD_YEAR = {2007}>}) *p({<ORD_YEAR = {2008}>}) *p({<ORD_YEAR = {2009}>}) *

p({<ORD_YEAR = {2010}>}) *p({<ORD_YEAR = {2011}>})

DISTINCT ACCOUNT)

Regards,

Stefan

Re: SET Analysis - Baisc Question

Fri, 23 Sep 2011 17:40:32 GMT

Thanks Stefan!

It worked!

SET Analysis - Baisc Question

Fri, 23 Sep 2011 18:22:15 GMT

Hi,

but may u tell me why somthing like this <...>*<..> in this case doesn't work?

I've used this sometime and it works fine, but here not..

Thanks.

Stefano.

Re: SET Analysis - Baisc Question

swuehl — Fri, 23 Sep 2011 18:44:21 GMT

Sure,

The sets

$<PRODUCT={DVD}, ORD_YEAR = {2007}>,

$<PRODUCT={DVD}, ORD_YEAR = {2008}>

...

will return disjoint record sets, right (because ORD_YEAR can not be 2007, 2008, .. at the same time at record level)?

But if I ask for Accounts who have links to ORD_YEAR = 2007, ORD_2008 .. I will get records sets that have records in common, so the intersection will return the wanted results.

edit: the editor losts some lines, re-added

Re: SET Analysis - Baisc Question

Fri, 23 Sep 2011 21:04:49 GMT

Thanks for the explanation

swuehl wrote:
Sure,
The sets
$<PRODUCT={DVD}, ORD_YEAR = {2007}>,
$<PRODUCT={DVD}, ORD_YEAR = {2008}>
...
will return disjoint record sets, right (because ORD_YEAR can not be 2007, 2008, .. at the same time at record level)?

But if I ask for Accounts who have links to ORD_YEAR = 2007, ORD_2008 .. I will get records sets that have records in common, so the intersection will return the wanted results.
edit: the editor losts some lines, re-added

Re: SET Analysis - Baisc Question

Fri, 23 Sep 2011 21:34:45 GMT

Looks like its NOT working

I added one more line to the data set

A002

O-019

2008

I-014

BOOK

$6.00

Now it returns 3 tecords

Re: SET Analysis - Baisc Question

Fri, 23 Sep 2011 21:41:34 GMT

looks like the problem is its fetching All DVD accounts first , then look for accounts with sales in 2007, 2008 , 2009, 2010 and 2011.

So A002 qualifies

SET Analysis - Baisc Question

swuehl — Fri, 23 Sep 2011 21:53:01 GMT

I think this should be correct:

concat(

{ $<

ACCOUNT= p({<ORD_YEAR = {2007}, PRODUCT={DVD}>}) *p({<ORD_YEAR = {2008},PRODUCT={DVD}>}) *p({<ORD_YEAR = {2009},PRODUCT={DVD}>}) *

p({<ORD_YEAR = {2010},PRODUCT={DVD}>}) *p({<ORD_YEAR = {2011},PRODUCT={DVD}>})

DISTINCT ACCOUNT, '- ')

Stefan

Re: SET Analysis - Baisc Question

swuehl — Fri, 23 Sep 2011 21:56:11 GMT

You will notice that you will also get a count of 1 for Book, that's because Account 1 qualifies for DVD buys over all 5 years, but also is linked to Book.

And if you don't want to have it like this, you could add the PRODUCT={DVD} again:

count(

{ $<

PRODUCT={DVD},

ACCOUNT= p({<ORD_YEAR = {2007}, PRODUCT={DVD}>}) *p({<ORD_YEAR = {2008},PRODUCT={DVD}>}) *p({<ORD_YEAR = {2009},PRODUCT={DVD}>}) *p({<ORD_YEAR = {2010},PRODUCT={DVD}>}) *p({<ORD_YEAR = {2011},PRODUCT={DVD}>})

DISTINCT ACCOUNT, '- ')

This should be it,

Stefan

Re: SET Analysis - Baisc Question

Tue, 27 Sep 2011 14:15:56 GMT

Thank you Stefan!