Is it possible to "freeze/lock" selections and use the freezed selection in another pivottable to show all data?
This is a little difficult to explain but I will try.. The thing that i'm trying to do is this.
First select top 25 articles based on sales for the whole data (company). Then I would like to see how this 25 articles are sold in a Region. Which salesreps are not selling the top 25 articles? But when I make the selection in the field Region with REG04. it will show only 21 articles in the pivottable. But I have made a selection of 25 articles.?
The reason for this that the region REG04 has not sold all of the 25 articles. 4 articles are not sold at all. I would like in to e to find this 4 articles. And also have this 4 articles in a different color or format.
I would like to have this as a dynamic solution. Somtetimes it's top 50 articles based on BusinessArea. Somtimes top 10 articles based on the sales of Bill and so on.
Any suggestions? Alternating state? A datamodel with the salesdata in two different tables that not are connected? Aggr-functions?
I have attached a sample qvw-file and also excelfile with sample data.
Thank you for your input. I can see that you have used left join in the script and by doing that you will create new records for the null / missing rows when joining the other data . That could be a possible solution. In the real application I'm developing it's more data and more fields. I will try this with left joins and see what happens with the performance of the real application. Maybee it will solve the issue.