Local Editing Formula Reference

Formula Overview

GuanReport is an extended chart type embedded in Guandata BI. This document describes formulas used in local editing mode. In addition to built-in Excel formulas, GuanReport supports native formulas and derived formulas.

Syntax Requirements

Local editing formulas always start with the English ampersand symbol &.

Native Formulas in Local Editing Mode

Vertical Expansion

Syntax

&=viewName.fieldName
or
&=[viewName].[fieldName]

Use vertical expansion to fill field values downward.

Horizontal Expansion

Syntax

&=viewName.fieldName(horizontal)
or
&=[viewName].[fieldName](horizontal)

Use horizontal expansion to fill field values to the right.

Group Data

Syntax

&=viewName.fieldName(group:normal/merge/repeat)
or
&=[viewName].[fieldName](group:normal/merge/repeat)

Parameter	Description
normal	Display rows normally.
merge	Merge cells with the same group value.
repeat	Repeat the group value for every row.

Sort the grouping field before using group formulas. If sorting is not available, use derived formulas.

Inter-cell Calculation

Inter-cell formulas insert Excel formulas into cells.

Syntax

&=={columnIndex}{rowIndex} + operator + {columnIndex}{rowIndex}
&=&={columnIndex}{rowIndex} + operator + {columnIndex}{rowIndex}

• &== applies only to the current formula cell.
• &=&= applies to all cells in the column where the formula cell is located.

Parameter	Description
Column index	Can be an absolute column such as A or B, or a relative column. c means current column. Positive integers move right and negative integers move left.
Row index	r means current row. Positive integers move down and negative integers move up.
~(horizontal)	Optional. Add this suffix when inter-cell calculation needs horizontal expansion. Example: &=&={c}{r}/{c}{-1}~(horizontal).
~custom(<comparisonDerivedTable>,<skip>,noadd)	Optional. Use this when dynamic horizontal expansion and alternating inserted columns are needed. Do not use it together with horizontal.

Subtotal

Syntax

&=subtotalN:viewName.fieldName

N is the aggregate function code. For example, 9 means sum. Use this formula to calculate subtotals for grouped data when columns are static.

Copy Cell Style

Add the copystyle parameter to copy the style of the formula cell to expanded cells.

&=viewName.fieldName(copystyle)

Alternately Insert Row Data

Use skip and noadd together when data needs to be inserted on alternating rows. Because templates are processed from bottom to top, adding noadd to the first row can avoid inserting extra rows before alternating rows.

Derived Formulas in Local Editing Mode

Derived formulas are used to build pivot-table-like reports and are usually combined together.

Derived Table

When native formulas directly expand fields from the original table, they do not deduplicate or aggregate values. To build a pivot table, create a derived table first.

Syntax

&=[DT|sourceView!field1|field2...].derivedField

The derived table is generated from selected source fields and is used by later derived formulas.

Vertical Expansion Based on a Derived Table

Syntax

&&=expand_field(<derivedTable.derivedField>,<derivedFieldPosition>)

This expands a derived table field vertically and supports grouping and style copying.

Horizontal Expansion

Metric Names Below, Comparison Items Above

Use two formulas together.

Formula 1

&&=expand_field(<derivedTable.derivedField>,<derivedFieldPosition>,N,copystyle)

N is a positive integer indicating how many metric cells should be merged for each comparison item.

Formula 2

&&=expand_metric(<derivedTable>,<metricName1|metricName2...>)

This expands metric names below each comparison item. expand_field and expand_metric must use the same derived table.

Metric Names Above, Comparison Items Below

Syntax

&&=expand_metric(<derivedTable>,<metricName>,merge)

When there are multiple metrics, write separate formulas and use them together with guanlookupN.

Value Lookup and Aggregation

Use this formula to search data in the original table and return rows matching specified conditions, then aggregate the values.

Without a Comparison Derived Table

&&=guanlookupN([],<searchView>,<targetMetric>,<conditionField>)

With a Comparison Derived Table

&&=guanlookupN(<derivedTable>,<searchView>,<targetMetric>,<conditionField>,<conditionValuePosition>,<derivedField>,<derivedFieldPosition>)

Parameter	Description
N	Aggregate function code, such as 9 for sum.
searchView	Original view to search.
targetMetric	Metric field to query and aggregate. Multiple metrics can be separated by `
conditionField	Field used as lookup condition.
conditionValuePosition	Cell position containing the condition value.
derivedField, derivedFieldPosition	Fields and positions from the comparison derived table.
~custom(<comparisonDerivedTable>,,noadd)	Optional. Add this suffix when dynamic horizontal expansion and alternating inserted columns are needed.

Subtotal Calculation

Use this formula when a dynamically expanded column requires subtotal calculation. If the column is static, use the native subtotal formula.

&&=subtotalN(derivedTable.derivedField(Label:<label>,LabelPosition:<labelPosition>),<derivedTable>,N,copystyle)

Column Grand Total Calculation

Use this formula when a dynamically expanded column requires column total calculation.

&&=grandtotal9(<derivedTable>,<range>)

The range can be a single cell range such as C1:C1 or a multi-cell range such as C1:C4. If a subtotal formula is used, enter the range from the guanlookup cell to the subtotal cell. If not, enter the guanlookup cell range.

Row Total Calculation

Use this formula when dynamically expanded columns require row total calculation.

&&=columnTotal9(<derivedTable>,<derivedFieldPosition>,<metricIntervalColumns>,<horizontalOffset>)

Parameter	Description
derivedTable, derivedFieldPosition	Identifies the derived table and the position of the derived field in Excel.
metricIntervalColumns	Number of columns skipped between metrics.
horizontalOffset	Offset used to locate expanded cells.

Derived Formula Combination Example

A typical combination is to filter selected regions, compare data across multiple years, calculate values by city, and add subtotals and totals.

Recommended steps:

1. Create a GuanReport card and select the dataset.
2. Prepare dimensions such as region, city, and year, and metrics such as tax-included price and retail price.
3. Design fixed headers in Excel.
4. Use a derived table and vertical expansion for region and city.
5. Use horizontal expansion for year and metrics.
6. Use guanlookupN for value lookup and aggregation.
7. Add subtotal formulas.
8. Add grand total and row total formulas.
9. Upload the template, preview, and save.

Download template: Custom Formula Combination.xlsx