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