Calculation syntax
Calculation formulas are written in MDX syntax, but only support a small subset of MDX functions and operators. They can only operate on other metrics in the model, and must return a numeric value. You can do simple math operations to combine metrics, or simple comparison operations to return a given metric value when certain conditions are met.
Syntax specifics
If you are used to creating formulas in Tableau, be aware that calculation formulas in AtScale use the unique names for dimensional hierarchies, levels, and secondary attributes, rather than their label/display name. The label is the name that appears in BI tools. The unique name is the name that is used for the object in queries.
For example, a single Date dimension might have two hierarchies, Date Month Hierarchy and Date Week Hierarchy, which contain the following levels.
| Date Month Hierarchy | Date Week Hierarchy |
|---|---|
| Year | Year |
| Quarter | Week |
| Month | Day |
| Day |
Although both hierarchies have levels named Year and Day, to prevent ambiguities in queries, the levels should have different unique names, as in this example.
| Level | Query Name |
|---|---|
| Date Month Hierarchy/Year | YearMonth |
| Date Month Hierarchy/Day | DayMonth |
| Date Month Hierarchy/Year | YearWeek |
| Date Month Hierarchy/Day | DayWeek |
To see the query name for a hierarchy, level, or secondary attribute, open the Dimension properties panel for the dimension it belongs to and locate the object in the Hierarchies section. The names displayed there are the unique names for each object.