Creating Interactive Calendars in Xcelsius

By Loren Abdulezer

Fig. #1

Last month I showed you how to do some clever things with Sliders. This month I want to show a simple but effective facility in Xcelsius. The component we'll be concentrating on is the Interactive Calendar. (Please note: the Interactive Calendar component is only available on Xcelsius XL Professional and Enterprise XE products.)

Figure 1 shows an example of the Interactive Calendar in action. If you are viewing this article online you will be able to use the component.

Setting up your spreadsheet for the Interactive Calendar

The Interactive Calendar component makes it easy to view data over a desired time period. The example I would like to use involves data on page views and hits to a web site over a range of dates. These appear in columns A, B, and C in Figure 2.

Fig. #2

It would be nice to view selections of this data over any given week. This is handled in the table at E5:L7 on the sample spreadsheet. The highlighted cell (located at F5 on the spreadsheet) is the beginning date for the range of dates you're interested in viewing. There is no formula for this cell, as its value is revised every time you click on a different date in the calendar.

All the dates appearing to its immediate right (cells G5:L5) are set by incrementing the selected date by 1.

The values for page views and hits are looked up using the Excel VLOOKUP function. Since the spreadsheet file is available HERE, I'll allow you to go through all the formulas including VLOOKUP on your own.

Setting up an Interactive Calendar

In your Xcelsius work area, drag and drop the Interactive Calendar component onto the canvas. Double-click on it to open its Properties Panel, and set your Insert Source Data as shown in Figure 3.

Fig. #3

For the moment, I am going to hold off on doing more work on the calendar component. We'll add the finishing touches shortly.

Putting the Calendar Component to Work

Now it's time use the date selected by clicking on the calendar to show some information. Drop a Column Chart component onto the Xcelsius canvas. Go to its Properties Panel, and select the Date Range to be cells F5:L7 (see Figure 4).

Fig. #4

While we haven't yet added the finishing touches, if you preview it you can generate Xcelsius charts that will allow you to visually sift through the data over any range of dates.

The Finishing Touches

Notice that I said "any range of dates". But, is that always a good thing? You may want to restrict the range of dates in your calendar component so that it does not, figuratively speaking, run off the road. You can do this by setting the Interactive Calendar's behavior from the Behavior tab in its Properties Panel. There are two ways of doing this. One of these is to set the calendar's behavior to a “hardwired” range of dates. The other is to have it read the limits directly off the spreadsheet (see Figure 5).

Fig. #5

In this case, I've done both of these techniques. I have set the start date to read the values directly from the spreadsheet, and I entered the end date by hand.

These are the basic concepts and techniques involved with Interactive Calendars. Like so many components you can use in Xcelsius, there is really nothing magical about how to work them, but their effects are almost magical.

Loren Abdulezer is the CEO of Evolving Technologies Corporation (ETC), and author of the best-selling Excel Best Practices for Business. ETC, an Xcelsius Consulting Partner, is a technology consulting firm based in New York City. More information about Xcelsius can be found on Loren's web site: XcelsiusBestPractices.com. He can be reached at la@evolvingtech.com. ©2005 Evolving Technologies Corporation - all rights reserved. The files referenced in this article can be found in the Article Reprints section of: http://www.xcelsiusbestpractices.com