I ran into problems trying to solve the following requirement. Probably sombeody can help point me into the right direction.
I have a basic sales report whicht shows in a table turnovers by Region | ArticleGroup | Country.
Now it's required to summarize all countries which are below 1% of the total turnover per ArticleGroup into "Others".
See example here:
I managed to calculate the percentage per country withing the block.
[percentage] = [NetSales]*100 / ([NetSales] In ([Region];[ArticleGroup]))
Then I wanted to create a new variable dimensions that groups Country by that percentage.
[country_other] = IF [percentage] In([Region];[ArticleGroup];[Country]) <= 1 or IsNull([percentage] In([Region];[ArticleGroup];[Country])) THEN "Others" ELSE [Country]
But that didn't work. It works fine as long as the [Country] is still in the table (but then it does not sumarize the countries). But as soon as I remove [Country] dimensions from the table I get #Multivalue errors.
I'm working on Business Objects 4.2 SP5 P7 Web Intelligence on BEx Query (BW 7.6 P3)
As what I did so far did not work at all, I'm totally open for any suggestions.
Thanks in advance.