8 Replies Latest reply: Feb 19, 2013 8:41 AM by fredrik.tham

# Intersection of dimension in chart?

Hello,

I'm trying to create a chart that only contains the products that have been sold every year. Consider this simple example:

Year     Product     Sale

2008     A               1

2008     B               1

2009     A               1

2010     A               1

2010     B               1

2011     C               1

The result I''m looking for is:

Year     Product     Sale

2008     A               1

2008     B               1

2010     A               1

2010     B               1

It seems to be a simple union but having tried every possible, except the right one, set analysis trick in the book and can't get it to work. Any suggestions?

Thanks

Fredrik

• ###### Re: Intersection of dimension in chart?

hi

A:

year, product, sale

2008, A, 1

2008, B, 1

2009, A, 1

2010, A, 1

2010, B, 1

2011, C, 1

];

b:

product as newProduct,

sale as newSale

Resident A Where year ='2008' or year ='2010';

then output like this

 newYear newProduct newSale 2008 A 1 2008 B 1 2010 A 1 2010 B 1
• ###### Re: Intersection of dimension in chart?

Hello,

Thanks for your quick response but unfortunately it has to be done dynamically in a chart. That is not in the script.

// Fredrik

• ###### Re: Intersection of dimension in chart?

Sorry, I don't get what you want.

None of your products A, B or C is sold in every year (2008,2009,2010 and 2011), so your resulting table should be empty, right?

• ###### Re: Intersection of dimension in chart?

Hello,

Correct, my misstake. This is what the example should look like (I removed 2008 to make it easier):

Year     Product     Sale

2009     A               1

2009     B               1

2010     A               1

2010     B               1

2010     C               1

The result I''m looking for is:

Year     Product     Sale

2009     A               1

2009     B               1

2010     A               1

2010     B               1

or¨

Year     Sum(Sale)

2009     2

2010     2

Thanks

Fredrik

• ###### Re: Intersection of dimension in chart?

See attached qvw

• ###### Re: Intersection of dimension in chart?

Hello Gysbert,

Good idea but it doesn't solve the problem. Your example only checks that the product has been sold the same number of years not the same years which is what i need.

Cheers

Fredrik

• ###### Re: Intersection of dimension in chart?

Fredrik,

it depends on what you mean with every year (all years in your data model vs. selected years).

If your user needs to select years to look at, just change the variable vYearCount to

=count(distinct Year)

(remove the set identifier 1, or change the set expression to only consider selections in certain fields).

Instead of the calculated dimension, another approach would be using a set expression like

=sum({<Product= {"=count(distinct Year)=count(total distinct Year)"}>}Sale)

Regards,

Stefan

• ###### Re: Intersection of dimension in chart?

Hello,

Sorry for late response but I've travelling and stuck in endless meetings...

Thanks everyone for all the help - Stefan your solution with set analysis solved it for me. Actually I didn't know that it was possible to define a set that way. Really good to know.

Cheers

Fredrik