SET Analysis - Baisc Question

[#1]

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

[swuehl]

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

[#3]

Thanks Stefan!

It worked!

[#4]

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.

[swuehl]

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

[#6]

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

[#7]

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

[#8]

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

[swuehl]

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

[swuehl · Accepted solution]

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