Lets say I have 100 counties in a dimension object and census in a measure. I want to build a pie chart that shows the Top 10 counties by census, which is easy to do with adding a rank on the pie. Doing that doesn’t look right however, because the slices are sized based on the census of those 10 counties only.
Therefore, I want to add an 11th slice with “Other Counties” so the Top 10 are proportional to the entire pie/census. I tried adding a dimension object:
Unfortunately, none of the suggestions worked for me. I have the same problem as this guy quoted below. I tried doing what Ese said, but still get the multivalue and context error when using just the two objects with the additional “IN” reference after rank. Ese says I should get the top values in the dimensions and #multivalue in the “Other” dimension, but I only get #Multivalue (I did confirm my variable is a forced dimension).
This is XIR2 WEBI, can anyone try to see if they can do this and let me know if they succeeded?
Yes, the top 5 is a dimension, and I tried bring in just the two objects in a table, but end up with only a single row with #multivalue.
I have multidata providers and the dimension for the top 5 is joined which could be an issue, so I tried a fresh report with 1 data provider and just a few objects.
I built these two objects
Top 5: =If([Rank]<6;[Group ID];“Other”) In([Group ID])
Rank: =Rank([Total Mbrs];[Group ID])
When I put Top 5 and [Total Mbrs] in a table by themselves, I get #Multivalue on Top 5. If I add a third column and put [Group ID] in it, then Top 5 Displays properly with the “OTHER” for the non-Top 5. Removing [Group ID] gets me #multivalue again. Top 5 is definitely a dimension and Rank is definitely a measure.
Can you paste your two formulas exactly like you have them working now? Also, are you doing this in WEBI XI R2? I can get it working in DESKI, but not WEBI R2.
I noticed that when I closed the report and reopened it, the Top 5 object became a measure again, even if I had previously forced it to a dimension.
So I tried making it a Detail, and voila, it worked!
I still got #Multivalue on the “Other” row, but when I dragged the Pie Chart template onto table, the “Other” slice magically appeared. Then if I dragged the Vertical Table template onto the pie, the “Other” slice stayed in the table, removing the #Multivalue. Webi can be a funny tool like that sometimes.
And for any other poor souls who have to do this, here is a final formula that I used below. Splitting out the Rank formula to a measure object that you then reference in the Top X Detail Object works as well, though for educational purposes, it’s all in one formula below. You MUST make the Top X a Detail object (not a Dimension).
The reason for the IsError is because I noticed that if there was a Tie of the rank in the Top X, a #Multivalue would creep in separate from “OTHER”. I couldn’t find a way to handle Tie’s sadly, but by wrapping an IsError, I could at least bucket the ties into “OTHER”, which is fine for my purposes.
Another Tip, if the dimension you are using in your Top X is merged with other data providers, the “IN ()” operator must reference the merged object, not a specific data provider.
Ok, the Tie situation doesn’t work as I thought. I don’t have time to write the full solution at the moment, but it involved having to concatenate the [Group ID] dimension with the sum of Mbrs IN([Group ID]) and formatnumber with lots of 000000000s, then using this new measure in the Rank Measure instead of the original Mbrs measure .
This solves ties and assigns an actual rank to them, eliminating the #multivalue. This is VERY important to do, beacuse if Rank 1 and 2 are tied, they will get thrown into “Other” with my solution above. FORWARNED
its pretty rare to have a tie in ranking (and you bumped into one). i would suggest to extend its values to decimal, that way they could differ (unless they too have the same decimal point value), just a suggestion
Arthur, good to know you are on R3. We are finishing an upgrade to that soon, so I’m happy to know this should work as a dimension. Will update thread when I find that out for sure (probably a few months).
jvm, unfortunately a decimal will not work as the measure is always a whole number. Even if it was, I wouldn’t recommend that route cause there is a chance of a tie (though decimals rarer). Using a new dimension object that concants the measure and dimension does work with ranking alpha as long as extra zeros added in front.
Measure data looks like this when wrapped in FormatNumber( ; “0000000000”). Rank on this new measure object instead of original and apply rest of solution as normal to eliminate ties.
0000000113Los Angeles
0000000098San Diego
0000000064San Francisco
Please post more details. This thread has very detailed instructions on how to make this work. Where are you having a problem, what errors are you receiving, what have you tried already? Please post formula’s used.
Rakesh, it would be helpful for you to post exactly what you tried, but I will try to help you nonetheless. For starters, check your spelling on your Dimension object “Coountry”.
What version of BOXI are you running, R2 or 3.1? Are you trying to do this in WEBI or DESKI? This thread is about getting this to work in WEBI.
Create a Meausure object like this named “Rank Country”:
=Rank([Revenue];[Country])
Create a Detail object (not dimension) named “Top Country” like this:
=If([Rank Country]<6;[Country];“Other”) In([Country])
Put the “Top Country” object and your regular Revenue object into your report, then drag the pie chart template over the table and it should work.
Alternatively, you can create a single Detail (not dimension) object like below and drag it into your report with the regular Revenue object.
(BOB member since 2004-12-08)
(BOB member since 2009-07-22)
(BOB member since 2009-08-03)
(BOB member since 2004-12-08)
.
(BOB member since 2007-12-11)