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:

**=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!

very popular to u!......................................................................

ReplyDeletepleasure to find such a good artical! please keep update!!.................................................................

ReplyDeleteQuality is better than quantity.................................................................

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

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

ReplyDelete..................................................................

教育無他，愛與榜樣而已............................................................

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

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

ReplyDeleteThanks. 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.

ReplyDelete累了嗎？來杯咖啡休息一下吧！............................................................

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

ReplyDeleteNecessity is the mother of invention..................................................................

ReplyDelete很棒，令人期待............................................................

ReplyDeleteMake hay while the sun shines...................................................

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

ReplyDeleteTHX FOR SHARE!!!感激呀!............................................................

ReplyDelete善言能贏得聽眾，善聽才能贏得朋友。......................................................................

ReplyDeleteThis comment has been removed by the author.

ReplyDeletehttp://musclevillage.com

ReplyDelete