Another good application of this function is in a chart calculation. If your calculation includes a dual, e.g.:

**Dual(Country & ' (' & Num(Sum(Sales)/Sum(Total Sales),'0.00%') & ')', Sum(Sales))**

Then you will have both a Number and a Text value - either or both can be displayed on the chart using the "Text on Datapoint" and/or the "Numbers on Datapoint" options in the Express Tab of the chart.

One use of this is to display 2 disfferent calculations on, say, a pie chart. In the above example, the Pie chart could be set to display both the total values and the percentages - something that is not achievable any other way.

This good idea does not work with latest beta of the v9 SR1

ReplyDeleteFMA

Yep. It has been broken in SR1.

ReplyDeleteThis is because they have replaced the 2 options of "Show Text on Data Point" and "Show Numbers on Data Point" with "Show Values on Data Point"

You can "fix" it by using:

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

Stephen

I am using the following expression:

ReplyDeleteDual(SOLDTONAME & '(' & Num(SUM(GROSSAMOUNT)/SUM(TOTAL GROSSAMOUNT),'0.00%') & ')'& chr(13)& Num(Sum(GROSSAMOUNT),'$#,###.##'),Sum(GROSSAMOUNT)) to create a pie chart. The problem I am having is that the "Others" category with values <2% isn't showing on the chart. I assume this is becauase of my expression working if I have a SOLDTONAME. Any thoughts how I can get the "Others" info to display? Thanks.

Just posted a response to this as a new article. Have a look at http://qliktips.blogspot.com/2010/06/more-on-dual-others-in-chart-using-aggr.html

ReplyDeleteYour additional information helped! Thanks :)

ReplyDeleteOne last problem: I need to re-position my data labels so they all are visible.

The expression I'm using is:

Dual(If(Len(SOLDTONAME)>0,SOLDTONAME,'OTHERS') & '(' & Num(SUM(GROSSAMOUNT)/SUM(TOTAL GROSSAMOUNT),'0.00%') & ')'& chr(13)& Num(Sum(GROSSAMOUNT),'$#,###.##'),Sum(GROSSAMOUNT))

The calculated dimension is:

=if(aggr(rank(SUM(GROSSAMOUNT)),SOLDTONAME)<=10,SOLDTONAME,'Others')

How can I post the image so you can visually see it?

I would say that 11 slices on a Pie chart is way too many and you are going to have problems with positioning of the text. My own opinion is that any more than 3 slices doesn't work.

ReplyDeleteA horizontal bar chart would work very well though - and you can keep the labels.

This comment has been removed by a blog administrator.

ReplyDelete