Xcelsius Thermometer Chart

In just a few quick and easy steps, you can generate a thermometer chart with out of the box Xcelsius functionality.
Here is how it looks all together with a line chart to toggle the goal line.

Continue reading →

Creating a Custom Theme in Xcelsius

For the past year as I have transitioned from creating dashboards in Excel to utilizing Xcelsius, I have been forced to regress on some of my previous charting advances. Spurred on by the teachings of Edward Tufte and Stephen Few, and the implementation of their ideas by BonaVista Systems and Juice Analytics, I was determined to make great “clean” dashboards.

Continue reading →

Adding Regression Trends to a Dashboard

Do you need to display the Linear Regression or Logorithmic Regression of data trends in an Xcelsius dashboard but don’t know how to write the required formulas? If so, you’ll be extremely pleased to know how easily these regression types can be created in Xcelsius. And all without having to figure out which formula to use, how to get the syntax perfect, or which values should be plugged into which variables. Just bind the component to two rows (data source and destination cells) in your spreadsheed and it’s done!

By using the Trend Analyzer component, you’ll be able to add up to five different types of regression trends to your dashboards – in seconds. This post will walk you through the steps of adding regression trends to a dashboard and discuss several other details of the component.

To save time, I’ll add the Trend Analyzer component to an existing Xcelsius model before describing the features, functionality, and properties. The screenshot below shows how to locate it in the component browser as it appears in the Other category.

* One thing to note about this component is that it’s not visible during runtime. You don’t have to worry with moving it to a bottom layer in your dashboard because it can only been seen while in development.

Selecting the Trend Analyzer Component

 

Modifying the Trend Analyzer Properties

Once you’ve added the Trend Analyzer component to your dashboard canvas, select the component to view it’s properties. The properties are separated into three sections and pictured in the screenshot below.

  • Data
  • Trend/Regression Type
  • Insertion Options

Trend Analyzer Properties

Properties: Step by Step

1. Data: The Data section is very important because this is where you will map (or bind) to the data that will be converted to the specified regression type. Just click the standard mapping icon to select the data to be analyzed. See screenshot below.

Bind to the Data to be Evaluated

2. Trend/Regression Type: Here you have the option of selecting either Use Best Fit or Select a Type. The default selection is Select a Type and the default selection type is Linear. The available selection types are:

  • Linear
  • Logorithmic
  • Polynomial
  • Power
  • Exponential

3. Analyzed Data Destination: Here is where you’ll map to a blank area in your spreadsheet that’ll be used to store the analyzed data fed in from the Data section. Be sure to color-code and label your Analyzed Data Destination cells or it will be difficult (if not impossible) to locate them later.

More Options
Additionally, you can click the underlined supplemental section called More Options to display a list of advanced or formula specific values. Use this section to add addional labeling to your dashboard if the formula values are important to your users.

These options include the following:

  • Equation Type Destination
  • Equation Destination
  • R2 Value Destination
  • F Value Destination

These properties are pictured below.

Additional Trend Analyzer Display Options

 

Putting the Trend Analyzer Component Into Action

It’s time to bind our component to some data values and see what it can do.

  • Step 1: Bind to data. I selected cells C2:H2 as the data source for the component.
  • Step 2: Select a Regression Type. I selected Use Best Fit.
  • Step 3: Define your Analyzed Data Destination. This step is used to bind to blank rows in your spreadsheet. It’s important to note that the number of cells selected in this step should match the number of cell selected in Step 1. I selected cells C1:H1.
  • Step 4: Highlight and label your destination cells. In this example, cells C1:H1.

To see the regression value displayed, add a new series to a chart then bind to cells C1:H1.

Trend Analyer Properties - Bound to Cells

 

Displaying the Variables
 
Follow these steps to display the variables used in creating the regression.

  • Step 1: Expand the More Options label
  • Step 2: Bind to a single blank cell in your spreadsheet for each of the four options
  • Step 3: Color code and label your spreadsheet
  • Step 4: Add Label components to your dashboard canvas and map to the cells used in Step 2.

Trend Analyzer Options - Bound to Cells

Putting it All Together

The next screenshot shows how a Regression series can be added to a chart and trend details displayed at the bottom of the screen. The drop-down was added to show that Trend/Regression values can be calculated dynamically and modified by other components in the dashboard. This simple example passes the Combo Box selection to a cell in the spreadsheet and VLOOKUP formulas are used in cells C2:H2 to determine the value to be charted. The formulas in cell C2:H2 are listed below.

  • C2 =VLOOKUP($B$2,$A$19:$O$22,3,FALSE)
  • D2 =VLOOKUP($B$2,$A$19:$O$22,4,FALSE)
  • E2 =VLOOKUP($B$2,$A$19:$O$22,5,FALSE)
  • F2 =VLOOKUP($B$2,$A$19:$O$22,6,FALSE)
  • G2 =VLOOKUP($B$2,$A$19:$O$22,7,FALSE)
  • H2 =VLOOKUP($B$2,$A$19:$O$22,8,FALSE)

If you recall, step 1 above (in Putting the Trend Analyzer Component Into Action), C2:H2 is the data source for our Trend Analyzer component. This means that the values in C1:H1 will always change when the contents of B2 is changed. Not coincidentally, cell B2 is the destination cell of my Combo Box.

Below is a screenshot of a simple dashboard using Trend Regression and displaying trend details.

Trend/Regression Used in Dynamic Xcelsius Chart and Dashboard

Please feel free to contact me with any questions.

See the dashboard in action: Interactive and Dynamic Regression Trending with Details

Download the XLF file: Regression Trending XLF

Thank you,
Jim Brogden
jim.brogden@daugherty.com

Label Based Menu Interaction Options

In this post we will review interaction options of the Label Based Menu component.

One of the nice features under the interaction options of the label based menu component is the “Expand On” feature. This turns the menu into a condensed button which when clicked or mouse over expands a list or menu similar to a web based fly menu.

This feature is great for gaining a little extra canvas space for the more important components such as the chart or indicators on the dashboard.

How to achieve:

1. On the behavior tab of the label based menu component uncheck “Always Expanded”

2. Choose whether or not you want to expand on mouse click or mouse over. I prefer mouse over.

3. Choose the direction you want to expand. For horizontal orientation you have the option to expand left or right. For vertical orientation you can choose from center down, left down, right down, center up, left up, or right up.

4. Open Animation gives an animated “Flash” feel when the menu expands.

There you have it. An easy way to gain some additional real estate on your canvas.

Please let me know if you have any questions and as always please feel free to download the source files.

Christmas Lights with Xcelsius

This post shows how to create Christmas lights in Xcelsius by using just a couple components. The two components used in this example are the Push Button and Play Control component. Just five formulas were written in the spreadsheet to alternate the colors and create “blinking lights”. These formulas are all based on the Data cell in the Play Control component.

Continue reading →