Samples, Tips, and Useful Cases

Basic Sample Expressions

The following are a set of calculated field sample expressions.

Function Name	Sample Dataset to Test Function	Expression	Sample Output
Opposite Value	HR Dataset	-[Wage]	-36,542.00 (for Joan Baez)
Age	HR Dataset	(today()-[BirthDate])/365	50.13 (for Joan Baez)
Name & Department	HR Dataset	[Fullname]& ", " &[Department]	Joan Baez, Development (for Joan Baez)
Sales Percentage	Samples	[New Sales]*100/sum([New Sales])	9,26% (for Japan)
Name starts with J	HR Dataset	if(find("j",lower([Fullname]),1)=1,"Starts with J",0)	Starts with J, 0
Deviation from Avg	HR Dataset	[Wage]-average([Wage])	-50476.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.

YoY Analysis: Comparing Revenue Figures for a 2-Year Period

You can create calculated fields, for example, to carry out a simple YOY analysis.

Let's take a look at the following dashboard, which has the different divisions 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 subtract the total difference for the year.

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

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