Creating calculated attribute and measure

Attribute:

An attribute refers to a characteristic or feature of a data point or object. Attributes describe the properties, qualities, or traits of the data. They can be categorical or numerical. For example, in a dataset of students, attributes could include age, gender, height, weight, and academic performance.

Measure:

A measure, on the other hand, is a value or quantity calculated from the data. Measures are used to quantify or describe the attributes of the data points. They provide a way to analyze and summarise the data. Examples of measures include mean (average), median (middle value), and mode (most frequent value). In business contexts, measures could be financial metrics like profit, revenue, or percentages.

Custom Attribute:

A custom attribute is a user-defined characteristic or feature that is not inherently present in the original dataset. Users can create these attributes to include additional information or categories based on their specific needs. For instance, in a dataset of employees, a custom attribute could be “Employee Tenure,” calculated based on the start date.

Custom Measure:

A custom measure is a user-defined calculation or derived value that is not part of the original dataset. Users can create custom measures to perform specific calculations on existing data points. For example, in a sales dataset, users might create a custom measure for “Profit Margin,” calculated as (Profit / Revenue) * 100 to express profit as a percentage of revenue.

In summary, custom attributes and measures provide flexibility for users to tailor their analysis and reporting to meet specific requirements or gain unique insights from the dataset.

Below are the steps to create a custom attribute:

Step 1: Open the Dashboard module, create a dashboard by entering a name, selecting a schema, and choosing a table.

Step 2: Select the three dots on the right of Attributes, then choose “Create Calculated Attribute.” This action will open a window for Calculated Attributes.

Calculate Attribute: To create a customized attribute according to the requirement.

Step 3: Enter the query language in the provided box, then validate and save it.

Below are the steps to create a custom measure:

Step 1: Select the three dots on the right of Measures, then choose “Calculated measure.” This action will open a window for Calculated Measure.

It is used to create a customized measure according to the requirement.

Step 2: Input your query language in the designated box based on your calculation requirement, then validate it.

Once you click the validate button, a confirmation message indicating “Calculation is valid” will appear if your query is correct. Upon successful validation, proceed by clicking “Save & close” to continue.

Step 3: You can now utilize the calculated measure for visualization and analysis purposes from the list of measures.

Note:
Variables created within dashboards cannot be used in Lumenore Ask Me queries. To create variables for use in Ask Me, go to Schema Manager, navigate to Variables, and create the required variable using Structured Query Language (SQL).

Using Functions

Symbols

Here are the symbols and their meanings:

• != or <>: Not equal to

It is used to compare if two values are not equal.

• <: Less than

It is used to check if one value is less than another.

• =: Equal to

It is used to check if two values are equal.

• <=: Less than or equal to

It is used to check if one value is less than or equal to another.

• >=: Greater than or equal to

It is used to check if one value is greater than or equal to another.

• >: Greater than

It is used to check if one value is greater than another.

• +: Addition

It is used to add two values.

• -: Subtraction

It is used to subtract the right operand from the left operand.

• *: Multiplication

It is used to multiply two values.

• /: Division

It is used to divide the left operand by the right operand.

Logics

• AND:

The logical AND operator. It is used to combine multiple conditions in a WHERE clause, and it returns true only if all the specified conditions are true.

• OR:

The logical OR operator. It is used to combine multiple conditions in a WHERE clause, and it returns true if at least one of the specified conditions is true.

• IN:

The IN operator. It is used to specify multiple values in a WHERE clause. It returns true if the value matches any value in the list.

Filters

• INCLUDE_FILTER / include_filter (INFTR):

These terms suggest the inclusion of certain data based on a filtering mechanism. For example, it might refer to a clause that specifies conditions for including certain records in a result set.
Include filters allow analysts to explicitly select dimension values or groups that must be part of the analysis before aggregation happens. They ensure that only the specified attributes contribute to the computed KPIs, improving accuracy and focusing insights on target segments.

Syntax:

{include_filter [Table].[Column] : AGGREGATE([Table].[Measure])}
{include_filter [Table].[Column] : ([Table].[Measure)}

Example:

{include_filter [Orders].[Region] : SUM([Orders].[Sales])}
{include_filter [Customer].[Country] : COUNT([Orders].[OrderID])}
{include_filter [Region].[Country],[Customer].[Segment] : SUM([Sales].[Amount])}

• EXCLUDE_FILTER / exclude_filter(EXFTR):

Similar to “INCLUDE_FILTER,” these terms imply excluding certain data based on a filter condition. It could be used in contexts where you want to filter out or exclude specific records from a dataset.
Exclude filters remove specific dimensions or categories from contributing to aggregated results. They are useful when certain data points are outliers, test samples, or irrelevant segments that would distort the actual performance analysis.

Syntax-

{exclude_filter [Table].[Column] : AGGREGATE([Table].[Measure])}
{exclude_filter [Table].[Column] : ([Table].[Measure)}

Example-

{exclude_filter [Products].[Category] : SUM([Sales].[Amount])}
{exclude_filter [Products].[SubCategory] : SUM([Orders].[Quantity])}

Conversion

• To varchar:

Converting a value or expression to the VARCHAR data type.

Syntax – Expr::VARCHAR

Example – SUM(SALES): VARCHAR

• To integer:

A type of conversion or casting operation where a value or expression is transformed into an integer data type.

Syntax – Expr::INTEGER

Example – (7 / 3): INTEGER

• To float:

A type conversion or casting operation where a value or expression is transformed into a floating-point or real number data type.

Syntax – Expr::FLOAT

Example – (7 / 3): FLOAT

Conditions

• DECODE/decode: Typically found in SQL, particularly in Oracle Database. It provides a way to perform conditional logic in queries.

• WHEN /when: Often used in conjunction with the CASE or SWITCH statement. Specifies conditions to be evaluated.

• CASE/case: A keyword is used to start a conditional statement. It’s part of constructs like CASE WHEN in SQL or switch in some programming languages.

• ELSE /else: Used in conjunction with CASE or IF to provide an alternative action if none of the previous conditions are met.

• ELSEIF /elseif: Used in some programming languages as an alternative to ELSE IF. It’s part of a series of conditions to be evaluated.

• END/end: Marks the end of a block of code, often used to conclude conditional statements like IF or CASE.

• IF /if: Introduces a conditional statement. The code inside the IF block is executed if the specified condition is true.

• THEN /then: Often used after the condition in IF statements to indicate the block of code that should be executed if the condition is true.

Let’s understand with the help of a few examples.

• For CASE, WHEN, ELSE, and END key:

Example – Changing ‘South’ to ‘SOUTH’ else ‘OTHER.’

• For IF and ELSEIF

Example – Labelling North as ‘first’, South as ‘Second’ and rest regions as ‘Other.’