Skip to content

Cell formatting in DetermTable that shows which cells are active

In this Analytica 5 Video Short,  I configure a DetermTable to use cell formatting so you can tell which data will be used by the computed result given the model's current selections. The solution uses a Cell Format Expression to figure out which cells aren't used, and to set their font color to gray.

DetermTables are input tables that keep around data for all cases, both cases that are currently selected as well as for unselected cases, but their result includes the data for only the selected cases. They are used in Analytica to keep inputs for different scenarios in your model, even though you may want to evaluate you more on only one or a subset of scenarios. We saw DetermTables in my previous Analytica 5 Video Short on MultiChoice controls. In fact, I start this video with the same model, where I left off in that video.

There are a lot of ways to use computed cell formats. I demonstrated a different application in a previous Analytica 5 Video Short on How to show negative numbers in red. Today's example is also simple example, which you may want to use in your own models, and which should also give you an additinoal a taste for how you use them. Computed cell formats are most often used in result tables, and will not be normally be used by an edit table. To use it in an edit table as I do here, you have to set the CellFormatFlags attribute for the edit table variable to 1. The extra step is there because you really need to think about your Cell Format Expression's complexity before you use them in edit tables. Because it needs to be evaluated often while a user edits cells in an edit table, you need to make sure your expression is fast.  In this video, mine certainly is.

(View directly on YouTube)

The Cell Format Expression from the video is:

    Cell Format Expression:
         If Not @[Projects = Domain of Projects] Then
        CellSpan( Domain of Projects,  CellFont( color:'LightGray' ) )

The @[I=x] operator returns to position of x in the index I, or returns 0 when it is not there. So it is also used as a test for “is x an element in index I?” So the IF part is testing each Project row of the DetermTable (i.e., each item of Domain of Projects) to see whether it is one of the selected projects. The Projects index is the list of selected projects. If not, then it sets the CellFont to light gray. Without the CellSpan( ), the default behavior is to apply the format only to the body cells of the table. The CellSpan here causes it to be applied to both the body cells and the header cells of the Domain of Projects index.

For a deeper introduction, see Computed Cell Formats on the Analytica wiki. And, keep an eye out for future Analytica 5 Video Shorts that showcase other examples.

Share now