Skip to Content
avatar image
Former Member

Xcelsius drill-down with crosstab data

I'm new to Xcelsius and can't work this out even though the concept seems simple enough so was hoping for some guidance.

I have a crosstab with countries as rows and cities as columns - the measure is the population of the city. What I wanted was a pie chart of the countries and the share they make up of the total population. That seems straightforward enough with tying the label of the chart to the that of the countries and the values to that of the total for each row. When clicking on the country, I then want to display, in a bar chart below the pie chart, the cities with corresponding population for the country selected in the pie chart.

The problem I have is that when I click a country in the pie chart, my bar chart shows all cities from the cross tab though only with bars for those cities corresponding to the selected country. I suppose the best way of describing it is the drill-down chart has static labels showing all cities with bars changing as I click on countries in the pie chart.

If I try to illustrate that with data:

Header 1 Header 2 Header 3 Header 4
New York London Total USA 8000000
8000000 England

8000000

8000000

The pie chart will show USA and England both at 50%. If I click USA, my bar chart will show a bar for New York with a value of 8000000 but London also appears on the x-axis. Similarly if I click England in the bar chart, a bar will show for London, no value for New York but it still appears as a label on the x-axis. I want for only those cities of the selected country to appear but I can't seem to do that. I'm hoping it's something really simple but I can't seem to make this happen. Any help will save what's left of my hair and is appreciated.

Cheers

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    avatar image
    Former Member
    Mar 15, 2013 at 03:40 PM

    Hi Jack,

    The solution for your question is (will use the picture attached to make it easier to understand):

    1. The Pie Chart should display the values in the Total Column. Enable Drill Down (or Data Insertion if you are using Dashboards aka Xcelsis 2011) and set it up like this: Insertion Type: Row, Data Source: the whole data set, excluding the city names and the totals column, Destination: wherever you want (best practice and common sense says to highlight it with a different colour so that you won't forget). In my example that is A7:G7.

    2. The row above your destination:  use the formula in the formula box (in case you can't see it, that is =if(B6>0,B1,"") ) for B6 and then drag it across to every cell above data pushed by the Pie Chart. In my picture there will be data in B7:G7 (A7 will be the name of the selected country). What that formula will do is to display the content of cells B1,C1,D1.... (city names) in B6,C6,D7... only if there is data in B7,C7,D7...

    3. The Bar Chart: Tick Data by Series. Display data in B7:G7, use B6:G6 as Labels. ! (what comes next is important) In the Behaviour Section choose both Ignore Data in Series and in Values.

    This solves your problem. Good Luck!

    Regards,

    Matt

    PS: If you want to be smarter set the title of the Bar Chart to A7 (which is the selected country)

    PS2: Just realised you can't see the row numbers. The first row (with city names) is row 1.


    example2.JPG (123.5 kB)
    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Hi Jack,

      I know this question was answered but just for your own information I think this might be helpful.

      You said that:

      I'd tried a combobox with filtered row insertion using data of the format you describe but I really wanted to be able to make the selection from the pie chart and not a combobox. I thought I might have been able to write the seletion from the pie chart to the combobox and then have that control the drill-down chart but no luck there either

      You went the right way there by trying to "feed" the selection of the pie chart into the combo box as this is what I would have done myself. Don't know why it didn't work but if you want to use that you need to go to Behaviour Section (of the Combo Box) and bind "Selected" to the value of a cell which is the output (or selection) of the pie chart. You need to make sure that the content of that cell would be one of the Labels of the Combo box.

      Good luck with your dashboard!

      Regards,

      Matt

  • avatar image
    Former Member
    Mar 15, 2013 at 03:15 PM

    Hi,

    I will start replicating your data and try to find a solution which does exactly what you want.

    Before doing that tho it is worth asking if the data can only be displayed in a crosstab. In Dashboards the best way to have your data would be something like:

    Column 1        Column 2           Column 3

    USA                 L.A.                 3m

    ENGLAND        London             5.5m

    GERMANY       Frankfurt           4m

    ENGLAND        Birmingham      3m

    USA                 New York         4.5m

    GERMANY       Hamburg          2m

    USA                 Chicago           3m

    In this form the data would be very easy to use (using Combo Boxes with Filtered Rows as Insertion Type and the whole Column 1 <country names> as Labels).

    As I said I will try finding a way to solve your problem the way you wanted it but if you could put your data in the format I suggested your life will be a lot easier.

    Regards,

    Matt

    Add comment
    10|10000 characters needed characters exceeded