SISENSE FORMULA REFERENCE

1 2014 SISENSE , Inc. | SISENSE FORMULA REFERENCE Formulas are custom calculations performed on one or more field in the data. They offer an important way to analyze results and express business logic. This guide provides an overview to working with formulas along with important tips and examples. SISENSE FORMULA capability is designed around several principles; Create complex business calculations without IT or technical knowledge. Easily combine fields from different data sources together. Customize formulas to reflect specific criteria and conditions Work with raw data with no need to summarize data sets before creating formulas. Instantly recalculate formulas based on any filter, variable or level of granularity. The table below provides a REFERENCE to the main FORMULA functions available in SISENSE .

2 GOAL FUNCTION TYPES AND SYNTAX Perform Calculation based on Criteria Measured Value Value Filters: ,=, >,<, between Text Filters: Contains/ Doesn t Contain/ Doesn t End With/ Doesn t Start With/ Ends With/ Start With/ Equals/ Not Equal List Filter: Include/Exclude Ranking Filters: Top/Bottom Ranking Time Filter: Date and Calendar Combine Data/ Apply Simple Mathematics Aggregate Functions Operator: +,-,*,/ Aggregate: Sum() Average: Avg() Count: Count()/ DupCount() Range: Max()/ Min()/ Summarize Data Statistical Function Central Tendency: Median()/ Mode()/ Largest() Std Deviation & Variance: Stdev()/ Stdevp()/ Varp()/ Var() Quartile & Percentile: Quartile()/ Percentile() Accumulate Data Rolling Sum/ Average Sum To Date: YTDSum()/ QTDSum()/ MTDSum() Avg To Date: YTDAvg()/ QTDAvg()/ MTDAvg() Rolling Period Sum & Avg.

3 : RPSum()/ RPAvg() Compare Time or Trends Time Functions Past Periods: PastYear()/ PastQuarter()/ PastMonth() / PastDay() / PastWeek() / Next()/ Prev()/ All() Growth Trend: Growth()/ GrowthRate()/ Contribution() Time Difference: YDiff()/ QDiff()/ MDiff()/ DDiff()/ HDiff()/ MnDiff()/ SDiff()/ Change: DiffPastYear()/ DiffPastMonth() / DiffPastQuarter() /DiffPastDay() 2014 SISENSE , Inc. | The FORMULA Editor When adding or editing a widget click on the button to open the FORMULA editor and create a new FORMULA . The FORMULA editor has two tabs, the Data Browser to select fields and the Functions Tab to select FORMULA operations. A FORMULA can be created combining one or more function, field and filter. The diagram below highlights the main components of the FORMULA panel.

4 1. Functions are operations which perform different calculations, for example a sum. 2. Fields in the Data Browser are variables contained in the data set (ElastiCube). Clicking on a field in the data browser will include it as part of the FORMULA . 3. Filters can be applied to restrict formulas based on criteria. 4. Starring is a way to save a FORMULA for later use. The rest of this guide provide more details and examples of how to use FORMULA functions. 2014 SISENSE , Inc. | Formulas Based on Criteria and Conditions Often formulas must take into account specific criteria. To do this SISENSE provides a feature called Measured Value which like the SUMIF function in Excel only performs a calculation when the values meet a set of criteria.

5 Criteria for Measured Values may be based on any logical operators in a filter. A simple example of Measured Value is the use of a list filter, a marketing team may need to count leads generated for a specific region such as North America. Even if leads come from many different countries they can create a measured value which totals the leads generated only when the lead originates from the United States or Canada. A more sophisticated case is the use of a ranking filter, for example a sales team may want to track the contribution of best-selling products to total revenue. However, what constitutes a popular product may change over time. A measured value can be created for sales which includes a condition that only shows sales for the top products for any month.

6 This simultaneously filters the data but also takes into account changes in what classifies as a top product over time. Measured Values are a powerful feature to take into account business logic and quickly perform calculations only when a specific set of criteria is met. Measure Value Syntax: (Measure, Scope1 , ) Parameters Measure: A field measure or FORMULA . Scope: A filter including Value, Text, List, Ranking and Time filters. Country # Leads USA 15 France 20 Canada 5 UK 30 Month Product Sales Jan A 10 Jan B 5 Jan C 15 Feb A 1 Feb B 25 Feb C 5 Example 2: Measured Value with Top Ranking Filter (Sum(Sales), (Top Ranking Filter: Top 2 Products by Sales)) Results Month Total Sales Top 2 Products Jan (A/C) 10 + 15 = 25 Feb (B/C) 25 + 5 = 30 Example 1: Measured Value with List Filter (Sum(Leads), (List Filter: Country = United States, Canada)) Result 15+5 = 20 2014 SISENSE , Inc.

7 | Functions to Build Formulas Functions are operations which perform common types of calculations and make it quick to build new formulas (for more online see the FORMULA Demos Page). Below is an overview of four types of functions, the associated FORMULA syntax and examples. Combine Data: Aggregate Functions Aggregations are used to perform mathematical calculations on data. Although this is an essential function SISENSE offers an advantage in the ability to run multiple aggregations on several fields simultaneously this makes it easy to summarize data based on multiple factors. An example simple aggregation is a sales manager who want to calculate the average sales revenue for each sales rep. They can create a pivot table which shows the sales rep and their average sales revenue.

8 A more complex example is a multi-pass aggregation which is an aggregation that performs multiple calculations simultaneously. Following our first example let s assume the sales manager wants to also see average sales per day for each sales rep. Instead of having to add an additional column for day in the pivot table the manager can create a multi-pass aggregation that first performs a sum of sales per day and then averages the results for each rep. This requires two fields - a day from a date field and the revenue field, as well as two aggregations, sum of sales and average. This result is the sales manager does not need to add a column for days in the pivot . Aggregate Syntax: Function(Numeric Field) Parameters Function: Sum(), Avg(), Count(), DupCount(), Max(), Min() Numeric Field: A numeric field or FORMULA .

9 Data Set Day Sales Rep Revenue 1 Jan John 10 1 Jan Jane 20 1 Jan Jane 40 2 Feb John 30 2 Feb John 10 2 Feb Jane 5 Results Example 1: Avg(Revenue) Sales Rep Avg Revenue John (10+30+10)/3 = Jane (20+40+5)/3 = Results Example 2: Avg(Days, Sum(Revenue)) Sales Rep Avg Daily Revenue John (10+(30+10))/2 = 25 Jane ((20+40)+5) /2 = 2014 SISENSE , Inc. | Summarize Data: Statistical Functions Descriptive statistics provide meaningful summaries of data and help make more informed decisions. This is particularly important for large data sets where descriptive statistics can help to focus analysis. An example of statistical functions is a marketing team that has a large data set on leads generated from various channels and want to understand where to focus their budget.

10 Descriptive statistics can be used to summarize valuable insight about each channel such as the central tendency or median leads generated along with standard deviations to assess typical lead volume. Accumulate Data: Running Total and Average Often to measure performance data must be viewed in a continuous and accumulative format over extended periods such as years, quarters or months. SISENSE provides functions to create running totals and averages over standard or custom time periods. For example, a support team has a goal to reduce the average monthly cost to resolve open issues. A Year to Date Average can be used to track progress towards reducing the average cost of support. Statistical Syntax: Function(Numeric Field) Parameters Function: Median(), Mode(), Largest(), Stdev(), Stdevp(), Varp(), Var(), Quartile(), Percentile() Numeric Field: A numeric field or FORMULA .