Computed cell formats

New to Analytica 5.0

You can use an expression to depict interesting features of the data in a table as a format for each cell, using font style, size, and color, fill color, text alignment, border style, number format, and bars within each cell. Computed formats offer a wide variety of creative ways to visualize what's important and interesting about your data.

Some examples

Here are a few examples to show some of what's possibe with computed cell formats:

A heat map emphasizes the numeric quantity, using green vs. red to show positive versus negative, and transparency/opaqueness to reflect the magnitude of the numbers in each cell.

Show numeric magnitude by varying font size:

Depicting quantity using cell bars:

Alternate row colors:

Use fill color and outer border to emphasize cells values with a certain property, in this case numbers that are divisible by 3:

Compute the indentation of index elements based on information in the model -- in this case, whether the element is a state, country or city. This can be easier that manually indenting the items from the dialog, and automatically adapts as you add locations to the hierarchy:

Tutorial video

Video: How to show negative numbers in red (time 2:30)

Cell Format Expressions

To specify a computed cell format, you enter the expression into the Cell Format Expression attribute. You may first need to make make this attribute visible in the Object window and Attribute pane. Open the Attributes dialog (on the Object menu), select Variable if needed, and and check Cell Format Expression in the list of attributes. Then you can enter and edit the expression in the Object's Object window or the Attribute pane.

By default, a computed cell format for a variable defined as an Edit table (or Subtable, Multitable, or DetermTable) applies to its result table, but not to its edit table. A computed format takes precedence over any manually selected formats set from the Cell format dialog.

Cell format expressions are just normal Analytica expressions, but with special functions called cell format predicates: CellAlignment, CellBar, CellBorder, CellFill, CellFont, CellNumberFormat, and CellOnClick. The CellFormats function lets you combine multiple predicates. The CellSpan function applies formats to header cells and totals. The CellComparisonFormat applies formats to external comparison variable columns.

Computing formats

Simplest example

The simplest example of a computed format would be an expression that contains a single predicate, and so applies the same format to every body cell:

CellFill('Green', 0.1)

The 0.1 is the alpha channel (on a scale from 0.0 to 1.0), and serves here to lighten the green fill color.

Highlighting largest row and column

Next, we use the CellFill('Green', 0.1) inside some logic to highlight the cells in the row and column with the largest sum:

Local si := Sum(Self, I);
Local sj := Sum(Self, J);
If si=Max(si, J)  or  sj=Max(sj, I) Then CellFill('Green', 0.1)

:

Notice that the expression references Self -- that is the result of the variable for which you are computing a format.

Highlight tails

In this example, we highlight the largerst 2 values along J in bold, and the lowest 2 values along J in italics.

Local r := Rank(Self,J);
If r<=2 Then CellFont(italics:true)
Else If r+2>IndexLength(J) Then CellFont(bold:true)

:

Combining multiple predicates: hiding zeros

This example uses CellFormats( ) to combine two predicates to change two properties -- font and the border. It hides cells with zeros by setting the font color to white and not showing their cell border on inner sides along I andJ:

If Self=0 Then CellFormats( 
       CellFont(color: 'White'),
       CellBorder('None', 1, 'Inner', I, J)
)

:

Combining multiple logic fragments

You can also use CellFormats to combine multiple logic fragments. In this example, the first fragment sets the cell fill for every other row along index I, and the second fragment sets the font color of negative numbers to red:

CellFormats(
     If Mod(@I,2) Then CellFill(0xeeeeee),
     If Self<0 Then CellFont(color:'Red')
)
 
:

Applying formats to Totals rows and columns

The cells that appear in a Totals row or Totals column are outside the range of coordinates that can be described using the array indexes and Self. To specify a format that applies only to the Totals row or column, use the CellSpan function with a position of zero. The syntax is:

CellSpan( I, fmt, 0 )

where you are specifying format for the total over the I index, and «fmt» is a cell format expression. The 0 is the third parameter identifies the totals.

To create a thick border (width 3 pixels) between the totals and the body along index I, you can use

CellSpan( I, CellBorder( 'Solid', 3, 'Near', I ), 0 )

:

To do this for both indexes

CellFormats(
     CellSpan( I, CellBorder( 'Solid', 3, 'Near', I ), 0 ),
     CellSpan( J, CellBorder( 'Solid', 3, 'Near', J ), 0 )
)

:

To set the borders of only the bottom right corner total (all sides)
CellSpan( I, CellSpan( J, CellBorder( 'Solid', 3, 'Both', I, J ), 0), 0 )

:

Notice that in these examples, both the CellSpan predicate and the CellBorder predicate expect an index, but these indexes are playing different roles. In CellSpan it is identifying which cells (i.e., totals row or totals column), whereas in CellBorder it is identifying which side of the cells that were identified by the outer CellSpan (i.e., top/bottom or left/right). This is demonstrated by this example where one index is passed to CellSpan and the other index to CellBorder:

CellSpan( J, CellBorder( 'Solid', 3, 'Both', I ), 0) 

:

Applying formats to header cells

To apply a format to the header cells for index I, use CellSpan( I, fmt, header:true ).

CellSpan( I, CellFill('Yellow'), header:true )

:

With the «header» parameter set to false, CellSpan( I, fmt, header:false ) applies the format only to body cells, not to header cells, which is what usually happens without the CellSpan unless the expression is nested inside another CellSpan that applies it to headers. To apply the format to both headers and body cells, omit the header parameter.

CellSpan( I, CellFill('Yellow') )

:

You can nest CellFormats inside (or outside) CellSpan. Here the CellSpan selects the totals row along «I», and the header cells of «I» as well as body cells (because the «header» parameter is omitted, and then within the CellSpan both fill and border are changed, so a CellFormats is used to combine them.

CellSpan( I, CellFormats( 
      CellFill('Yellow'), 
      CellBorder( 'Double', ,'Near', I, color:'Red' )
), 0)

:

Comparison variable columns

When you have comparison variables (or expressions) in your result table, CellComparisonFormat is used to select the column(s) that the format should apply to. The result for a variable y is shown here, and it the same table the results for variables x and u are also shown and have been added to this table as comparison variables. The Cell Format Expression is set to CellBar(Self), and since Self here is the result of y, the computed cell format has only information about y, but displays in the columns for x and u.

CellBar(Self)

:

The above is not the desired display, since the bars that appear in the x column reflect the value of y. To restrict these bars to the y column, use

CellComparisonFormat( CellBar(Self), Self )

:

To show the correct bars in each respective column

CellFormats(
    CellComparisonFormat( CellBar(Self), Self ),
    CellComparisonFormat( CellBar(x), x ),
    CellComparisonFormat( CellBar(u), u )
)

:

Interaction with manually set formats

When you have set some cell formats from the Cell format dialog, and also have computed formats, the computed formats take precedence. Hence, if you set the fill to red using the dialog, but your Cell Format Expression sets cell fill to green for the same cell, the cell will be green.

Auxilliary variables

Your Cell Format Expression can reference several values that provide information about the view being shown, as follows.

• Self: The value in each cell of the result or edit table. (For edit tables, these might be expressions).
• HorizIndex: Synonymous with the horizontal index in the current pivot.
• VertIndex: Synonymous with the Vertical index in the current pivot.
• ComparisonIndex: Synonymous with the comparison index, if any.
• ViewMode: The view type, one of: 'Mid', 'Mean', 'Sample', 'PDF', 'CDF', 'Statistics', 'Bands', 'Edit'.
• VertIndexes: A list of row indexes (currently either 0 or 1 handles in length).
• HorizIndexes: A list of column indexes (currently 0, 1, or 2 handles in length).
• SlicersSetToTotal: A list of handles to slicer indexes that are currently set to Total.

Finally, the list of slicer index settings can be obtained from the top level of your logic, but not necessarily from within nested expressions, using the GetEvaluationContext function. For example, GetEvaluationContext( ) returns the list of slicer indexes, except those set to Totals. Then, for any of these, GetEvaluationContext(h) returns the selected position along that index.

Alternating row fill

An example earlier on this page showed an alternating row fill along the I index. If you pivot the table and put I on the horizontal, the alternation becomes an alternation of column colors. And it the pivot is such that I is on neither axis, no fill alternation appears at all, because the alternation is associated with the index I.

Using the auxiliary variable VertIndex, you can alternative rows in such a way that the row color alternates no matter what index is on the vertical.

If Mod(@VertIndex,2) Then CellFill( 0xeeeeee ) Else CellFill( 0xffffcc )

:

and if the table is pivoted, it is still the row fill that alternates.

:

Although auxiliary variables are seldom used, outside the alternating row example, they do enable you to encode conditional logic that changes based on the view being displayed.

Edit Tables

By default, computed formats apply only to result tables, but static formats set from the Cell format dialog apply to both edit tables and result tables.

To apply your computed format to edit tables, right-click on your Cell Format Expression in the object window select Apply computed cell format to edit table. Internally this sets the CellFormatFlags attribute for the variable to 1. This is demonstrated in the following video

Computed formats should be applied to edit tables only in certain safe cases. Many problems can result, so you should take great care before doing so. First, when your logic references Self, in many cases the values might be expressions in the edit table view, not numbers as they would be in the result table. This is likely to cause errors if your logic does even very common operations on the value, such as computing a Sum or Max, or using the value where a number is expected. Hence, you usually only want to consider computed formats that are restricted to numeric entries only.

Another consideration is the computation time required to re-evaluate your cell format logic. The expression will need to be re-evaluated often, including after individual cell changes. In a huge table, an operation over the entire array can take a substantial amount of time, and cause your edit table to be unwieldy.

Your computed expression can use the value of ViewMode, which is set to 'Edit' when an edit table is showing, as a way to alter your logic for the edit table view.

If ViewMode='Edit' Then «expression used for edit tables» Else «expression used for result tables»

See Also

• Cell format dialog
• Predicates for specifying formats: CellAlignment, CellBar, CellBorder, CellFill, CellFont, CellNumberFormat, and CellOnClick
• Combining predicates or logic: CellFormats
• Selecting special cells (totals, headers, comparison columns): CellSpan, CellComparisonFormat
• Attributes dialog