I. Overview
1) Formula display is a type of display setting. It changes the display value of a cell and make no difference to the cell's actual value.
2) Prerequisite: Insert Data Columns and Insert Formulas.
| You will learn |
|---|
|
II. Steps
1. Create a new dataset [ds1]
1) Create a new DB query dataset
2) Drag the table [flow] into the DB query.
3) Preview. The 6th and 7th digits of the data column [Time] are displayed as the month, which is expressed in numbers.
2. Drag datasets into cells
1) Drag datasets [Time], [Inflow_rate] and [Outflow_rate] into B2~D2.
2) Set as center alignment and add borders.
3. Calculate the net flow
1) Insert a formula in E2 by inputting the formula C2-D2 in the text area in the Formula Definition pane.
2) Set as center alignment and add borders.
4. Preview
5. Set default display value
1) Select E2, click Cell Attributes->Display in the right pane, and select [Formula Display] in the drop-down box.
2) Click and enter if( $$$>0 , '/' , 'deficit' ) in the text area in the Formula Definition pane.
3) Preview. If the net flow is greater than 0, “/”will display, and if the net flow is less than or equal to 0, “deficit” will display.
6. Meaning of green triangle
A green triangle in the upper left corner of E2 indicates E2 has Display settings.
7. Modify the display format of month directly
1) Select B2 and add Formula Display
2) Input formula in the text area in the Formula Definition pane: mid($$$, 1, 4) + '-Nov-' + mid($$$, 9, 10)
3) Preview. Months are displayed as a three-letter abbreviation instead of numbers
8. Define the actual and display values of month by editing the dataset
1) Create a built-in dataset [Embedded1]
2) Set two character-type columns: [Actual] and [Display]
3) Insert a piece of data
Actural: 11
Display: Nov
9. Modify the display format of month by editing the dataset
1) Select B2 and modify the formula in the text area in the Formula Definition pane to:
mid($$$, 1, 4) + '-' + value("Embedded1", 2, 1, mid($$$, 6, 2), 1) + '-' + mid($$$, 9, 10)
2) Preview.
