Drill Down for Multiple Series in a Stacked Column chart

[Former Member]

Hello,

I have a problem with the Drill down functionality in a stacked column chart that I have been unable to solve.

The setup:

I have 6 plans on the X-axis (plan 1 - 6) with the count of members on the Y-axis. I have 6 series of groups that are displayed in the stacked column chart( group A, B, C, D, E, F) in each of the plans.

My requirement is to click on a series (E or F to be exact) and then display a spreadsheet table with data pertaining to that group under X plan. There are a total of 12 spreadsheet tables that the user is able to view, but only one table can be shown at a time (whichever the user selects).

I can do this simply by following the four steps below:

i) Enable drill down.

ii) Choose 'Position' as insertion type.

iii) Fill out the destination field.

iv) Make sure the spreadsheet tables get the data from the destination cell.

I can do this for one series (F) without problems, but when I try to do it for a second series (E) I get stuck.

XCelsius will not let me use the same destination cells for different series. So, I will have to use other destination cells.

Is there any way to achieve this functionality?

let me know if you need further information and thanks for your input. I have also attached an image of the stacked column chart.

[Former Member · Accepted solution]

Hello Geovanni,

You were correct to use Location in Drill Down. In addition you have to use "Series Name Destination" which will give you series selected.

I tried to create sample Column Chart based on scenario shared by you and successfully exact the values of Series and X-Axis value (i.e Plan) with the help of these value you can search the spreadsheet tables and accordingly map in destination.

Continue with Insertion Type as Position and get 6 Different location. from A-F Series. Then from "Series Name Destination" Cell get exact value of Position selected for Series.

Then you need to add first column in spreadsheet tables which will be concatenation of GroupName and Plan (e.g A1 ; B2 etc). With the help of Vlookup you can bring only one spreadsheet information in destination Column and show it to User.

Due to restriction i would not be able to attached out file but please find attached screen shot for your reference.

Let me know if this answer your query.

Regards,

Sushil Padhye

[Former Member · Accepted solution]

Hello,

Small correct in VLookup Formula, Instead of =VLOOKUP($J$2,Q6:R11,2,FALSE) use =VLOOKUP($R$3,Q6:R11,2,FALSE).. where R3 is the destination for "Series Name Destination"  option in Insertion property of Chart

Regards,

Sushil Padhye

[Former Member · Accepted solution]

Hi Sushil,

Thanks for your input.

I have a couple of questions:

1.Can you please provide more snapshots of the excel sheet showing the destination, component properties linking to the excel data? Like what cells would go under the Insertion tab for the stacked column chart, 6 series and their destination.

2.For the spreadsheet components (table lists) what are the values for the Dynamic visibility (key and status) and the Destination under General options in the spreadsheet component.

[Former Member · Accepted solution]

Hello,

Please find below step by step guide to create dashboard.

Getting Group Name and Plan Number based on Drill on Stacked Column Chart

1)   Create Dataset of Plan and Group to mapped with Stacked Column Chart at location $B$2 as per attached image 1.

2)   Map this Data to Stacked Column Chart in Dashboard as per attached image 2.

3)   Enable Insertion Option for the stacked chart and map Excel destination for “Series Name Destination” and Select Insertion Type as “Position”.

4)   Then map Series destination for 6 (Group) Series as from C13 to C18 each cell for each Series Destination. Finally if required select Default                   Selection.For Point 3 and 4 see attached image 3

5)   Finally to select Plan value write Vlookup in Cell C20 as shown in image 3. This will give you final Plan which is selected at run time.

6)   After all above step you will get Group Name and Plan Number Selected in C11 and C20 cell of Excel Respectively. If required map this will      Label Component to validate at Run time.

Selecting Spreadsheet data based on Group Name and Plan number obtained in step 6

1)   Create Spreadsheet data having all required information along with Group Name and Plan Number columns at beginning as shown in image 4      attached.

2)  In Addition to this Create calculated Column in J as shown in Image 4 which is concatenation of Column K and Column L.

3)  Create on Calculated Cell in C22 as shown in image 5

4)  Finally right formula of Vlookup (if you are having single value in spreadsheet for each Group and Plan) to get matched data of Group and Plan from          Datasheet created in Step 1 of this section in cell. See attached image 6 for formula.

5)  Now you have your final data based on Group Name and Plan selected from stacked chart in M42 to Q43.

6)  Finally you can map this to spreadsheet component in dashboard. See image 7

[Former Member · Accepted solution]

Since we can add only three images please find rest screenshot.

P.S : If you are okay with resolution please mark this as Resolved

[Former Member · Accepted solution]

Thanks Sushi for all your help!

[Former Member]

Can you please mark this as Resolved if you got answer what is expected??

[former_member182541]

Check whether this helps you.

Inspiration » Xcelsius: Advanced stacked bar chart drill down

[Former Member]

Thanks for your input Suman, but it doesn't really help me much. I am trying to add a spreadsheet(component) list with data (first name, last name , ssn, number, address, etc) rather than just a single value.