Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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

View solution in original post

8 Replies
Not applicable
Author

hi

A:

LOAD * INLINE [

    year, product, sale

    2008, A, 1

    2008, B, 1

    2009, A, 1

    2010, A, 1

    2010, B, 1

    2011, C, 1

];

b:

LOAD year as newYear,

    product as newProduct,

     sale as newSale

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

then output like this

newYearnewProductnewSale
2008A1
2008B1
2010A1
2010B1
Not applicable
Author

Hello,

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

// Fredrik

swuehl
MVP
MVP

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?

Not applicable
Author

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

Gysbert_Wassenaar

See attached qvw


talk is cheap, supply exceeds demand
Not applicable
Author

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

swuehl
MVP
MVP

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

Not applicable
Author

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