View source for Computed cell formats

[[category:Analytica 5.0]]
[[category:Concepts]]

''New to [[Analytica 5.0]]

== Overview ==
You can write and use an Analytica expression to compute cell-level formats for a table (usually a result table), thus enabling the appearance of the table to adapt to data and results in the model. Fill color, font style and color, text alignment, border style, number format, and cell bars can all be computed, with virtually unlimited possibilities for the sophistication of logic.

=== Some examples ===
Several examples of computed formats are show here is illustrate the potential application of computed formats.

:[[image:ComputedCellFormat_heatmap.png]]
A heat map can be used to emphasizes the numeric quantity, where green vs. red is used to reflect positive versus negative, and transparency/opaqueness used to emphasize magnitude of the numbers in each cell.

:[[image:ComputedCellFormat_fontSize.png]]
Emphasizing numeric magnitude using varying font size.

:[[image:ComputedCellFormat_bars.png]]
And yet another method, depicting quantity using cell bars.

:[[image:ComputedCellFormat_alternatingRowColor.png]]
Alternating row colors.

:[[image:ComputedCellFormat_divisibleBy3.png]]
Fill color and outer border emphasize cells containing values with a certain property, in this case numbers that are divisible by 3.

:[[image:ComputedCellFormat_indentation.png]]
The indentation for the elements of an index are computed 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 can automatically adapt to new locales that appear later.

== Cell Format Expressions ==
To use an expression to computed the cell formats, enter the expression into the '''Cell Format Expression''' attribute. To make this attribute visible in the [[Object window]] and [[Attribute pane]], go to the [[Attributes dialog]] (on the ''Object'' menu) and turn on  '''Cell Format Expression'''. Then edit the expression for your table from its [[Object window]] or from the [[Attribute pane]].

By default, your computed format will apply to the result table of the variable with the format expression, but not to the edit table. Your computed formats take precedence over any manually selected formats (set from the [[Cell format dialog]]).

Cell format expressions are just normal Analytica expressions, but with calls to special functions called ''cell format predicates'': [[CellAlignment]], [[CellBars]], [[CellBorder]], [[CellFill]], [[CellFont]], [[CellNumberFormat]], and [[CellOnClick]]. The [[CellFormats]] function is used to combine multiple predicates. The [[CellSpan]] function is used to apply formats to header cells and totals. The [[CellComparisonFormat]] is used to apply 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:
:<code>[[CellFill]]('Green', 0.1)</code>
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. With this [[Cell format expression]], every body cell contains a dim green fill.

:[[image:CellFormatExpression_SimplestExample.png]]

=== Highlighting largest row and column ===
Next, we use the <code>[[CellFill]]('Green', 0.1)</code> inside some logic. In this case, we'll highlight the cells in the row and column with the largest sum. The [[CellFormatExpression]] is now
 [[Var]] si := [[Sum]]([[Self]],I);
 [[Var]] sj := [[Sum]]([[Self]],J);
 [[If]] si=[[Max]](si,J)  or  sj=[[Max]](sj,I) [[Then]] [[CellFill]]('Green', 0.1)
 
 :[[image:ComputedCellFormat_Largest_I_and_J.png]]

Notice that the expression references [[Self]].

=== Highlight tails  ===
In this example, we put the top 5 values along <code>J</code> in bold, and the bottom 5 values along <code>J</code> in italics.
 [[Var]] r := [[Rank]]([[Self]],J);
 [[If]] r<=2 [[Then]] [[CellFont]](italics:true)
 [[Else]] [[If]] r+2>[[IndexLength]](J) [[Then]] [[CellFont]](bold:true)
 
 :[[image:ComputedCellFormat_HighlightTails.png]]

=== Combining multiple predicates: hiding zeros ===
This example uses [[CellFormats]]( ) to combine two predicates, so that two properties are changed together (the font and the border). The example hides cells with zeros in a sparse matrix by setting the font color to white and the border type for all inner sides along <code>I</code> and along <code>J</code> to <code>'None'</code>.

 [[If]] [[Self]]=0 [[Then]] [[CellFormats]]( 
        [[CellFont]](color:'White'),
        [[CellBorder]]('None',1,'Inner',I,J)
 )
 
 :[[image:ComputedCellFormat_HideZeros.png]]

=== Combining multiple logic fragments ===
In addition to combining cell predicates, the [[CellFormats]] function is also used to combine multiple logic fragments. In this example, the first logic fragment sets the cell fill for every other row along index <code>I</code>, and the second logic 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')
 )
  
 :[[image:ComputedCellFormat_MultipleLogic.png]]

== 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:
:<code>[[CellSpan]]( I, fmt, 0 )</code>
where you are specifying format for the total over the <code>I</code> 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 <code>I</code>, you can use
 [[CellSpan]]( I, [[CellBorder]]( 'Solid', 3, 'Near', I ), 0 )
 
 :[[image:ComputedCellFormat_TotalsBorder.png]]


To do this for both indexes
 [[CellFormats]](
      [[CellSpan]]( I, [[CellBorder]]( 'Solid', 3, 'Near', I ), 0 ),
      [[CellSpan]]( J, [[CellBorder]]( 'Solid', 3, 'Near', J ), 0 )
 )
 
 :[[image:ComputedCellFormat_TotalsBorder2.png]]

 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 )
 
 :[[image:ComputedCellFormat_TotalsBorder3.png]]

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) 
 
 :[[image:ComputedCellFormat_TotalsBorder4.png]]

== Applying formats to header cells ==
To apply a format to the header cells for index <code>I</code>, use <code>[[CellSpan]]( I, fmt, header:true )</code>.

 [[CellSpan]]( I, CellFill('Yellow'), header:true )
 
 :[[image:ComputedCellFormat_Header.png]]

With the «header» parameter set to false, <code>[[CellSpan]]( I, fmt, header:false )</code> 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') )
 
 :[[image:ComputedCellFormat_HeaderAndBody.png]]

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)
 
 :[[image:ComputedCellFormat_CFinsideCP.png]]

== Comparison variable columns ==
When you have [[XY_Comparison|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 <code>y</code> is shown here, and it the same table the results for variables <code>x</code> and <code>u</code> are also shown and have been added to this table as [[XY_Comparison|comparison variables]]. The Cell Format Expression is set to <code>[[CellBar]](Self)</code>, and since [[Self]] here is the result of <code>y</code>, the computed cell format has only information about <code>y</code>, but displays in the columns for <code>x</code> and <code>u</code>.

 [[CellBar]]([[Self]])
 
 :[[image:ComputedCellFormat_ComparisonVars1.png]]

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

 [[CellComparisonFormat]]( [[CellBar]]([[Self]]), [[Self]] )
 
 :[[image:ComputedCellFormat_ComparisonVars2.png]]

To show the correct bars in each respective column

 CellFormats(
     CellComparisonFormat( CellBar(Self), Self ),
     CellComparisonFormat( CellBar(x), x ),
     CellComparisonFormat( CellBar(u), u )
 )
 
 :[[image:ComputedCellFormat_ComparisonVars3.png]]

== See Also ==
* [[Cell format dialog]]