Monday 14 September 2009

More on Dual

I posted recently about the very useful Dual function (to store a numeric value and a text value - e.g. for Months).

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.

4 comments:

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

    FMA

    ReplyDelete
  2. Yep. It has been broken in SR1.

    This 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

    ReplyDelete
  3. 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

    ReplyDelete
  4. 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.

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

    ReplyDelete

Note: only a member of this blog may post a comment.