## Wednesday, 23 June 2010

### More on Dual - "Others" in chart using AGGR

In a previous blog post (More On Dual - http://qliktips.blogspot.com/2009/09/more-on-dual.html) I proposed a method for getting better labels in a Pie chart by using Dual to create the label and the value in the expression:

Dual(Country & ' (' & Num(Sum(Sales)/Sum(Total Sales),'0.00%') & ')' & chr(13) & Num(Sum(Sales),'#,##0'), Sum(Sales))

A problem has been pointed out to me by one of my readers - if the number of slices is restricted (10 by default), then the "Others" does not display correctly:

We can fix this by limiting the number of dimensions using an AGGR statement with RANK in a calculated dimension:

=If(aggr(rank(Sum(Sales)), Country) <= 4, Country, 'Others')

So here, I am checking if the rank of the dimension is less than or equal to 4. If so, just use the Country name, if not, use "Others".

Now, there is still a slight problem with this and the Dual - the name is still blank. So, we need to modify the expression to check for a blank name:

Dual(If(Len(Country)>0,Country,'Others') & ' (' & Num(Sum(Sales)/Sum(Total Sales),'0.00%') & ')' & chr(13) & Num(Sum(Sales),'#,##0'), Sum(Sales))

(It will be blank because there are actually multiple values).

So, all should be well!

Of course, this doesn't just apply to Pie charts (thankfully!) - this method of restricting the dimension using Rank and AGGR can be used in any chart - including Straight Tables!

1. pleasure to find such a good artical! please keep update!!.................................................................

2. 人有兩眼一舌，是為了觀察倍於說話的緣故。............................................................

3. Unable to give you a heart. so have a reply to push up your post.
..................................................................

4. 一棵樹除非在春天開了花，否則難望在秋天結果。..................................................

5. 人生中最好的禮物就是屬於自己的一部份..................................................

6. Thanks. I found that idea useful in creating a 'top 10 items' calculated dimension for a straight table chart. I added it to a cycle group. It's easier for my report users than typing in the syntax to select the top 10 items.

7. 大肚能容，了卻人間多少事，滿腔歡喜，笑開天下古今愁。..................................................

8. 良好的開端，已是成功的一半。..................................................

9. This comment has been removed by the author.