CalcDim - chart function

Overview

In this example, you will:

• Add an inline table and load data into an app.

• Create a table to visualize the data and perform calculations.

• Make selections in the table to observe the difference in behavior.

Open the Data load editor and add the following load script to a new section.

The load script loads an inline table. This table includes two fields: a field for the individual's first name, and a field for the individual's last name.

Load script

Names:

						Load * inline [
						FirstName,LastName
						John,Doe
						Amanda,Honda
						Amalia,Craig
						Cart,Lynch
					];

Table creation

Load the data and open a sheet. Create a new table and add these fields as dimensions:

• FirstName

• LastName

Next, add each of the following expressions as dimensions in the table:

• =FirstName & ' ' & LastName

• =CalcDim(FirstName & ' ' & LastName)

The table should look like the following.

Comparison: Making selections

In the =FirstName & ' ' & LastName column of the table, select the value John Doe. Notice that in the selection toolbar, the value is selected as a value from a field named =FirstName&' '&LastName.

Clear the selection.

In the =CalcDim(FirstName & ' ' & LastName) column of the table, select the value John Doe. In the selection toolbar, the value is selected as values from two different fields: FirstName and LastName.

These two expressions present the same information: a concatenation of the FirstName and LastName fields. The difference is that without CalcDim(), a synthetic field is created to store the calculation. With CalcDim(), the original fields from the data model are used as the basis for the calculated dimension.

Overview

In this example, we create dynamic dimension expressions referencing a variable, rather than references to static values.

In this example, you will:

• Add an inline table and load data into app.

• Create a variable to dynamically store a value.

• Create a table to visualize the data and perform calculations.

• Make selections in the table to observe the difference in behavior.

Open the Data load editor and add the following load script to a new section.

The load script loads an inline table. This table includes a date field and a calculated field, Year, which is derived from the raw dates.

Load script

SET DateFormat='MM/DD/YYYY';
						
						Transactions:

						Load *,

						RowNo() as ID,
						Year(date) as Year
						;

						Load * Inline
						[
						date,amount
						'1/19/2022',37.23
						'2/28/2022',88.27
						'3/16/2022',53.80
						'4/1/2022',82.06
						'5/16/2022',87.21
						'6/26/2022',45.89
						'7/9/2022',36.23
						'8/2/2022',76.11
						'9/26/2022',84.21
						'10/29/2022',67.67
						'1/1/2023',100.49
						'2/24/2023',99.47
						'3/6/2023',50.02
						'4/10/2023',2.05
						'5/20/2023',75.64
						'6/25/2023',98.11
						'7/19/2023',30.00
						'9/27/2023',76.11
						'10/26/2023',43.21
						'11/12/2023',135.41
						'1/15/2024',67.24
					];

Variable creation

Load the data and open a sheet. Create a variable named vCutoffYear, and give it the following Definition: 2023

Table creation

Load the data and open a sheet. Create a new table and add these fields as dimensions:

• date

• Year

• amount

Next, add each of the following expressions as dimensions in the table:

• =if(Year < $(vCutoffYear), 'Older', Year)

• =CalcDim(if(Year < $(vCutoffYear), 'Older', Year))

The table should look like the following.

Comparisons, part 1: Behavior without CalcDim()

In the above table, there is no difference between the two calculated dimension columns.

In the if(Year < $(vCutoffYear), 'Older', Year) column of the table, select the value 2023. Notice that in the selection toolbar, the value is selected as a value from a field named =if(Year<2023,'Older',Year).

Next, change the Definition of the vCutoffYear variable to 2024. Notice that while the selection field =if(Year<2023,'Older',Year) is the same and retains the selected value 2023, the data in the table has changed. The value 2023 has changed to the value Older. If you select Older in the if(Year < $(vCutoffYear), 'Older', Year) column, the selection Older is made in a new field: =if(Year<2024,'Older',Year).

In essence, a new field is internally created for each value of the vCutoffYear variable. When working with concurrent and changing selections, this behavior might not be desirable and can add complexity to data interpretation.

Comparisons, part 2: Behavior with CalcDim()

Now that you have seen the behavior of the expression when not wrapped in CalcDim(), clear all selections in the app. Reset the vCutoffYear variable to the value 2023.

In the =CalcDim(if(Year < $(vCutoffYear), 'Older', Year)) column of the table, select the value 2023. Notice that in the selection toolbar, the value is selected as a value from the underlying field, Year.

Next, change the Definition of the vCutoffYear variable to 2024. The selection field Year is the same, it retains the selected value 2023, and the data in the table has changed, as seen without CalcDim(). However, if you now select Older in the =CalcDim(if(Year < $(vCutoffYear), 'Older', Year)) column, the value 2024 is selected in Year.

All selections you make in a calculated dimension wrapped with CalcDim() will be reflected as selections in the underlying fields referenced from the original data model. This can simplify data interpretation and prevents cluttered, complex expressions from appearing in the selection toolbar when users consume the analytics content.