Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
newYear | newProduct | newSale |
2008 | A | 1 |
2008 | B | 1 |
2010 | A | 1 |
2010 | B | 1 |
Hello,
Thanks for your quick response but unfortunately it has to be done dynamically in a chart. That is not in the script.
// Fredrik
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?
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
See attached qvw
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
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
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