Working with Tilde Formulas

Last updated on 2025-12-11

Overview

Tilde formulas are used to create Excel reports. A tilde formula defines which data are to be queried from Lucanet and read into MS Excel.

A tilde formula is inserted into each cell of a reporting template where the queried data should be shown in the final report.

Tilde formulas always start with a tilde (~) and can contain placeholders, functions and statements. 

 

Example: Instead of manually entering Revenue: 150,000 into a table in Excel, you write a tilde formula in a cell, and Lucanet automatically inserts the current value of the revenue into the cell.

Structure of Tilde Formulas

Tilde formulas are structured as follows:

~Function/Statement(Para­meter_1=­­"Value";...;Parameter_n="Value_n")

 

Components of a Tilde Formula
A tilde formula consists of the following components:

Placeholder

Description

Function

Specifies what type of information is queried, such as value ("I want to retrieve a value from the database”). 

Unlike placeholders, which only represent values specified by the user, functions retrieve actual data from Lucanet.

Functions contain basic parameters that further specify the query and can contain optional parameters.

Parameter

A question or specification in a formula (e.g. Which account)

Value

The answer to a parameter's question (e.g., Revenue)

Keyword

A word that is recognized by the tilde (e.g., Item)

Placeholder

A variable that is replaced by actual values (e.g., ~Month)

In tilde formulas, placeholders can be used as substitutes for data levels, adjustment levels, organization elements, periods, partners, transaction currencies, display currencies, and time intervals, or as parameters of functions or statements.  

Cell reference

A reference to one or more columns in Excel (e.g., A2, B:D)

Statement

A command that changes the report structure (e.g., Fill rows)

Rules for Tilde Formulas
  • A tilde formula contains either a function or a statement with parameters (see section Functions and section Statements). 
  • A value must be assigned to a parameter with an equal sign ( = ).
  • Parameters are separated by a semicolon. 
  • Values for parameters must be specified in inverted commas ( " ).
  • It is also possible to use placeholders as parameters (see section Placeholders).
  • No distinction is made between upper case and lower case – unless explicitly specified.
  • A tilde formula can use spaces, hyphens ( - ) and underscores ( _ ).
Example

A tilde formula can be structured as follows, for example:

~Value(Item="Revenue"; Period=~Month)

 

Lucanet processes this tilde formula as follows:

  1. Understands that you want a value
  2. Identifies item as "Revenue"
  3. Uses the month selected in the reporting template (e.g., January 2024)
  4. Queries the database
  5. Returns as a result, e.g. 150,000 (the actual revenue value for January 2024)

 

 

Contact Us