Creating a Date Slider
By Ryan Goodman
Download the source files for this model HERE
The Crystal Xcelsius dual slider component is a very powerful, visual tool for allowing the model end-user to easily adjust minimum and maximum values. In this example, we have used the dual slider as a way for dynamically setting a date range for trend data. To achieve this dynamic date range capability, I combined the dual slider with Excel functions discussed in the previous articles and the Crystal Xcelsius “Ignore End Blanks” feature.
The following is a breakdown of how the Excel worksheet was setup for Crystal Xcelsius. We will take a reverse engineering approach to explore how the Excel functions were combined and linked to Crystal Xcelsius components.
Raw Data
Excel: Monthly data values for each product. You can simply insert your own dates and trend data into the raw data columns.
Xcelsius: The Label Based Menu will use this raw data as its Source Data
Insert in Column
Excel: Color code the Insert-In column yellow to signify that this will become cells used as input.
Xcelsius: The label based menu will use the range in column “I” as the Insert-In range.Date
Excel: Link directly to the date represented within the Raw Data (#1).
Xcelsius: No direct linkage to Crystal Xcelsius components.Lookup
Excel: These ascending values are referenced in a VLOOKUP formula in Lookup Value (#9).
Xcelsius: No direct linkage to Crystal Xcelsius components.Min Insert
Excel: Minimum value determined by the dual slider component. The insert in cell (B3) is highlighted in yellow. A formula contained in the next cell (B4) determines the date, based on the insert-in value for the minimum value.
Xcelsius: Within the dual slider component, link the “Low Data.”
Max Insert
Excel: Maximum value determined by the dual slider component. The insert in (C3) cell is highlighted in yellow. A formula contained in the next cell (C4) determines the date, based on the insert-in value for the maximum value.
Xcelsius: Within the dual slider component, link the “High Data”.First available row
Excel: States the minimum date (E1) and interval (F1) available from the data represented in the Date (#3) and Lookup (#4) columns.
Xcelsius: No direct linkage to Crystal Xcelsius components.
Last available row
Excel: States the maximum date (E2) and interval (F2) available from the data represented in the Date (#3) and Lookup (#4) columns.
Xcelsius: No direct linkage to Crystal Xcelsius components.Lookup value
Excel: Ascending value depending on the presence of Last Available row and Max Insert values.
Cell A7 logic =IF(A6>=$C$3,"",IF(A6>$F$2,"",A6+1))
If the previous value(A6) is greater than the than the Max Insert, A7 will be blank. If the previous value (A6) is greater than the Last Available Row, A7 will be blank. Otherwise A7 will = A6+1.
Xcelsius: No direct linkage to Crystal Xcelsius components.Lookup date
Excel: The lookup date is derived from the Date (#3) Column based on the Excel functions.
Cell B7 logic =IF(A7="","",VLOOKUP(A7,$G$6:$I$105,2))
If A7 is blank, than B7 will be blank. Otherwise a VLOOKUP will be performed based on the Lookup Value in cell A7.
Xcelsius: The chart’s Category Axis Labels are linked directly to the Lookup Date. Within the Behavior tab, the Values: Ignore End Blanks is selected to make the chart dynamic.Lookup trend
Excel: Trend data derived from the Insert in Column (#2) based on the Excel functions.
Cell C7 logic =IF(A7="","",VLOOKUP(A7,$G$6:$I$105,3))
If A7 is blank, than C7 will be blank. Otherwise a VLOOKUP will be performed based on the Lookup Value in cell A7.
Xcelsius: The chart’s trend data is linked directly to the Lookup Trend.
Trend YTD
Excel: Calculates the YTD derived from the Lookup Value (#9) based on the Excel functions.
Cell D7 logic =IF(C7="","",C7+D6)
If A7 is blank, than D7 will be blank, Otherwise the YTD value will = C7 + the previous YTD value in cell D6.
Xcelsius: A second graph is overlaid on top of the original graph referencing the Lookup trend (#11) data. This second graph has the same configuration, but the Source Data is linked to the Trend YTD data.Monthly/YTD Dynamic Visibility
Excel: Insert In cell that serves as a trigger to toggle the dynamic visibility between two graphs (Monthly=1 and YTD=2). This cell, A2, is highlighted in orange.
Xcelsius: The radio button used to toggle between the Monthly and YTD graphs uses cell A2 as the “Insert-In” row. The dynamic visibility for both graphs then uses cell A2 as the “Display Status” cell.
Chart Title
Excel: The chart title is the first row within the Insert in Column (#2).
Xcelsius: Link the chart title to cell I5 within the Insert in Column (#2).
Assembling the Model in Crystal Xcelsius
Inserting and configuring two graphs
After inserting, overlaying and linking both graphs with monthly and YTD data, make sure that the Behaviors tab has been configured correctly. “Ignore End Blanks” should be enabled, and the Dynamic visibility is setup to use cell A2 as the “Display Status,” with the correct Display Status Keys. (Monthly Graph=1 and YTD Graph=2)
Dynamic Subtitle
I used a series of labels and positioned them on the canvas to create dynamic subtitle based on the user’s selection. Most of the labels are entered as text strings within Crystal Xcelsius and rest are linked to cells (“date from” & “date to”). The end result is a dynamic subtitle that explains the date range for the given trend.
Configuring the Dual Slider
After inserting the slider and linking the low data and high data within the “General” tab, configure the Behaviors tab with fixed minimum and maximum values. Set the minimum value to “1”, and the maximum value to cell C1 which so it can dynamically change based on how many rows of data there are for each product. To finalize the dual slider, I manually added and linked the minimum (E1) and maximum (E2) labels to either side of the dual slider.
Minimum and Maximum Spinner
In addition to using the dual slider component, I used the combination of the Spinner and Label components to allow for a secondary method to adjust the month ranges. To achieve this effect, I overlaid the label on the spinner component.
The end result was an effective way to view up to 72 points within a trend and dynamically narrow the time frame through a click and drag interface.