This topic includes the following information:
The following are a set of calculated field sample expressions.
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.
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:
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")
Note: The "Interest Rate" variable must be of Type "Number" when using it to make calculations.
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.
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:
You have more than one column with Revenue information.
You have a Revenue column and a Date 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.
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.
In the following dashboard, the information on Revenue is organized in four different columns, with the months for the 2016/2017 period.
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
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:
Create a FIND
calculated field named "Find 2016 in Month" for FIND("2016", [Month], 1)
.
Create an IF
calculated field named "If Month Contains 2016" integrating the calculated field you just created:
Then, create the AVERAGEIF
calculation: