create layered chart using check box function

[Former Member]

I am new to using excelsius and I am trying to create a dashboard that looks like this one: http://www.analysisfactory.com/security3.html. I am not sure how to create the different layers using the checkbox function. I am assuming that I should be able to create one chart and have each check box point to a different row. But I might be wrong. The data I am looking at will be similar to the data in the example. I would like the check boxes to show fiscal years and then the chart to break down costs by month. Any help would be greatly appreciated!

[Former Member]

Maybe there is an easier way to realise this, but my first idea is to use multiple charts as layers. You can control the visibility of each layer (chart) with the dynamic visibility option of the chart components. The checkboxes are used to trigger the dynamic visibility key-value.

let you inspire by the following links:

http://www.xcelsiusjournal.com/index.php?option=com_content&task=view&id=51&Itemid=2

http://ryangoodman.net/blog/index.php/2008/02/28/overlay_charts_in_xcelsius

Best Regards,

Ulrich

http://www.xcelsius-insight.com

[Former Member]

I tried using layers to solve my problem but was not successful. One problem is with grid lines...the grid lines will write over the text of another chart. The other problem is I would like the scales to change dynamically with the data that is being shown like the example. Any other suggestions?

[Former Member]

Thomas,

I saw the analysis factory example and wanted to do the same thing. Here's how I went about it:

• I pointed the destination cell reference of the check box to the cell above the column I wanted to trend.

• I then selected a destination column outside of my data range where i wanted the data for the chart to be

• In my destination rage I wrote an if statement to say =if(destination cell of check ='s "1", pull data in corresponding cell, "") basically if the box is checked pull data in the column & cell if not check noting.

• In your chart, point the range to the various columns where your if statement is written and when the box is check your data will pull. This enbles your axes to be dynamic with each selection, and avoids the mess of layering charts.

Hope that helps.

[former_member212749]

Hi Scott,

I am new to Xcelsius.

Can you please help me in the below answer.Could you please tell me the formula that you used in the Excel excatly. I am not clear with the Pull Data in corresponding cell ...

How to get the data to a corresponding cell.

In my destination rage I wrote an if statement to say =if(destination cell of check ='s "1", pull data in corresponding cell, "") basically if the box is checked pull data in the column & cell if not check noting.

Regards

Prashant

[Former Member]

pull data in corresponding cell will point to the appropriate source for your data. so if you data is in source sheet called Source and ranges from A1 to D12 then your formula would be

if(Control!$A$1=1;Source!A1,"")

assuming that Control!A1 is where your checkbox writes to and Source!A1 is your first cell..

This would then be copied for all cells.

Hope this helps

Alan