insights-creating-formulas-based-on-criteria-and-conditions-filters

Creating Formulas Based on Criteria and Conditions (Filters)

Often formulas must take into account specific criteria. In Insights there is a feature called Measured Value; which is similar to the SUMIF function in Excel; and only performs a calculation when the values meet a set of criteria.

Criteria for Measured Values may be based on any logical operators in a filter.

Measured value

Measured Values are a powerful feature that take into account business logic and quickly perform calculations only when a specific set of criteria is met.

1. Create your formula from the Data Browser and Functions; as explained in Insights - Formula editor.

• Use the count function for whatever is inside the []. Remove letters COUNT from the outside so your formula looks like this:

1. Add the field (criteria) by which you want to filter the formula. Right-click the field and select Filter.

   

2. Filter the formula if needed. When done; click OK.
3. Put round brackets around your formula to complete the formula

The syntax below works too for this case; but if you use multi-select fields you might get duplicates; thus formula above is more reliable.

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; the measured value calculates 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. This simultaneously filters the data but also takes into account changes in what classifies as a top product over time.

The above example as defined in the Formula Editor.

Calculating Contributions Using the ALL Function

The All() function returns the total amount for a dimension; and can be used for various use cases. In the following example; we will use the All function to calculate how much each country contributed towards the total cost of a campaign.

Our final widget includes the following information:

1. The second column above represents a formula that sums up the total cost for all countries; but not the breakdown per country. The formula includes the calculation (total cost) followed by the all function (filter); followed by the dimension (country) in parenthesis. It looks like this:

We can save (star) the above formula and call it Total Cost for Countries; which will be used in the next step.

1. We can now use the above formula in another formula to calculate the contribution; like this:

The result is the third column above (plus formatting the results as percentages).