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.

7 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. I am using the following expression:
    Dual(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.

    ReplyDelete
  4. 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
  5. Your additional information helped! Thanks :)

    One 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?

    ReplyDelete
  6. 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
  7. This comment has been removed by a blog administrator.

    ReplyDelete