Version

Samples, Tips, and Useful Cases

Basic Sample Expressions

The following are a set of calculated field sample expressions.

Function Name Sample Dashboard to Test Function Expression Sample Output

Opposite Value

HR Dashboard

-[Wage]

-36,452.00 (for Joan Baez)

Age

HR Dashboard

(TODAY()-[BirthDate])/365

45 (for Joan Baez)

Name & Department

HR Dashboard

[Fullname]& ", " &[Department]

Joan Baez, Development (for Joan Baez)

Sales Percentage

Retail

[Sales]*100/SUM([Sales])

2,62% (for Alabama)

Sales Percentage 2

Retail

[Sales2]*100/SUM([Sales2])

1,85% (for Alabama)

Sales Percentage Delta

Retail

[Sales Percentage 2]-[Sales Percentage]/([Sales Percentage]*100)

0,85% (for Alabama)

Name starts with J

HR Dashboard

IF(FIND("j",LOWER([Fullname]),1)=1,"Starts with J",0)

Starts with J, 0

Deviation from Avg

HR Dashboard

[Wage]-AVERAGE([Wage])

-50,476.71 (for Joan Baez)

Converting Unix TimeStamps to Usable Dates

Unix times, defined in the seconds elapsed since January 1st, 1970 ("Epoch" time) are particularly useful because they represent all timezones at once. You can import data sources with unix timestamps and convert them into usable dates with the DATE formula.

((([Unix Time Stamp]/60)/60)/24)DATE(1970,1,1)([Timezone]/24)

Where:

  • Original Field: [Unix Time Stamp]

  • Convert to Minutes: /60

  • Convert to Hours: /60

  • Convert to Day: /24

  • Adding Epoch Time: +DATE(1970,1,1)

  • Adding Timezone: +([Timezone]/24)

The timezone can be entered as a number, or you can use one of your fields with a number. In either case, it must be GMT time.

Dashboard Variables and Calculated Fields

A particular widget can also reference Dashboard Variables through calculated fields using a new function (VARIABLE). This function receives a text parameter with the name of the referenced dashboard variable. However, this function can also be combined with other calculated fields. Let’s take the following dashboard as an example:

SampleDashboardFinance_Desktop

The Interest Rate variable is a generic number meant to reference the amount of interest for the period referenced throughout the dashboard (Nov-2016 to Oct-2017). Using a VARIABLE calculated field, you can, for example, calculate a division between the Net Sales and the current Interest Rate.

SUM([Net Sales])/VARIABLE("Interest Rate")

ResultingWidgetVariable_Desktop

Note: The "Interest Rate" variable must be of Type "Number" when using it to make calculations.

Simplifying Complex Calculated Fields

For complex or long calculated fields, consider creating multiple calculated fields and then combining them. This is particularly helpful when embedding multiple IF statements in a single calculated field.

This use case creates two complex AVERAGEIF calculations, and then divides both results to peform a YOY revenue analysis. There is an explanation on how to simplify the syntax for both AVERAGEIF statements.

YoY Analysis: Comparing Revenue Figures for a 2 Year Period

You can create calculated fields, for example, to carry out a simple YOY analysis. If you want to compare the Revenue for a 1-year period (or more). You might face one of two scenarios:

  1. You have more than one column with Revenue information.

  2. You have a Revenue column and a Date column.

Case 1: More than one Revenue Column

Let’s take a look at the following dashboard, which has the different products for a company and the revenue they represented during two different years.

YoyAnalysisRevenue_Desktop.png

You can compare the two figures by using the following calculated field. The "-1" is used to substract the total difference for the year.

([Revenue 2017]/[Revenue 2016])-1

You can then either keep the number, or format it as a percentage.

YoyAnalysisPercentage_Desktop

Case 2: Revenue and Date Columns

In the following dashboard, the information on Revenue is organized in four different columns, with the months for the 2016/2017 period.

YoyAnalysisRevenue2_Desktop.png

You would need to perform different calculations to first get the Revenue for 2016 and 2017 (SUMIF), and then use a different calculation, similar to the one in Case 1, for the YoY analysis.

  • Revenue for 2016. In order to calculate the total Revenue for 2016 for Product A, you will need to sum the values when "Month" contains "2016". You can do so by using the following calculation:

    AVERAGEIF([Revenue Product A], IF(FIND("2016", [Month], 1), 1, 0))

    The AVERAGEIF calculation requires an expression and an if condition:

    • expression: [Revenue Product A]

    • if-condition: the condition that needs to be met for the average to be calculated. This IF statement will look for 2016 in the Month column. If it present, it will calculate the average value. If it is not, it will exclude those rows from the calculation.

      • logical test: (FIND("2016", [Month], 1).

      • value if true: 1

      • value if false: 0

  • Revenue for 2017. In order to calculate the total Revenue for 2017 for Product A, you can use a similar formula than the one used for 2016.

    AVERAGEIF([Revenue Product A], IF(FIND("2017", [Month], 1), 1, 0))

This will return two columns with the average for 2017 and for 2016 for Product A. Then, use the calculated field in case 1 to compare the two figures. Make sure to use the actual names of the calculated fields you created in the formula below.

([Revenue 2017]/[Revenue 2016])-1

Simplifying the syntax for the AVERAGEIF statements

Let’s take a look at the Revenue calculations, which are constructed in the same way:

  • AVERAGEIF([Revenue Product A], IF(FIND("2016", [Month], 1), 1, 0))

  • AVERAGEIF([Revenue Product A], IF(FIND("2017", [Month], 1), 1, 0))

The formula might be difficult to read, as there is a FIND statement embedded in the IF condition to calculate the AVERAGEIF. We can create each calculated field separately in the following way:

  1. Create a FIND calculated field named "Find 2016 in Month" for FIND("2016", [Month], 1).

    CreatingExpressionSimple_Desktop.png
  1. Create an IF calculated field named "If Month Contains 2016" integrating the calculated field you just created:

    CreatingExpressionSimple2_Desktop.png
  1. Then, create the AVERAGEIF calculation:

    CreatingExpressionSimple3_Desktop.png