Creating KPI Views: Step-by-Step Guide with Cheat Sheet Formulas

Creating a dynamic self-updating dashboard is a time-saver for analysts. This reduces the time it takes to get from a data refresh to a polished visualisation to a couple of seconds.

KPI views are a great addition to reports - KPIs highlight the totals and show periodic changes - sometimes all the decision-makers need to comprehend the latest business performance.

Below are some formulas to help you enrich your reports and add dynamic KPIs to your dashboard.

There is also an article on this portal explaining the use of calculations in different areas of the Omniscope application; however, here, we'll cover just some KPI examples that you can immediately implement in your reports.

How to create a KPI view - steps 1-2-3

1. Add a Content view from the Plus menu. 

2. Pick the dataset from the Data tab menu. 

The calculated KPI values will be based on the Data and Query subset choices you select in the Settings menu.

From Settings > Query options, pick one of the following:

'Filtered in': The default setting, where the calculation is based on the filtered data. Anything filtered out will be ignored.

'Selection': The result will be based on the data selected in another view (e.g., a bar, marker, or pie slice in a chart based on the same dataset).

'All data': The calculation will be performed on the entire dataset and will not respond to filtering on the tab. Simply delete the 'Filtered in' default by clicking the bin button next to it.

3. Create some dynamic content!

Use a simple text editor to create and format a title, then integrate your dynamic text by adding formulas.

If it’s a basic function (like sum, mean, min, max, etc.) based on a single field, you can use the drop-down menu to select the field name and function (e.g., [Issue Date] with minimum).

For more advanced calculations, there is also a formula editor interface under Custom formula. This provides access to a wide range of functions, allowing you to perform more complex calculations within this space.

By clicking on the 'Custom formula' you will open the Formula builder interface, where you can use the drop-down menus to add formula syntax and insert fields / variables from your dataset.

In Omniscope's Content view, everything can be formula-driven, including text size, font choice, text colour, arrows, and more. This allows users to create KPI views with varying styles depending on performance. For example, you can use red italic text to highlight unsatisfactory performance or green text for positive results.

Static text and formulas can be combined to create dynamic statements that update automatically based on the latest results. For instance: "This week's sales were £250,865, which is 4% higher than last week" (with formulas driving the numbers and the word "higher").

Additional extra: consider using Content view's option "Insert View" to add a mini chart next to your KPI, to provide additional insight into time series fluctuations or KPI value distribution.

The 'cheat sheet'

Here are some examples of formulas that you may wish to adapt to your use case.

Please note that, due to the absence of a tabular data structure in Content view, referencing is done using Subset functions with specific conditions. This allows Omniscope to determine which subset of data should be used to perform a given statistical function. Additionally, the SAFEDIV function is used to handle division by zero, where you can specify a calculation result if the divisor equals zero.

The percentage difference between last month and the month before (% MoM difference):

SAFEDIV(

        (

            SUBSET_sum([No_Users_Impacted], subset([Rank month], 1)) -

            SUBSET_sum([No_Users_Impacted], subset([Rank month], 2))

        ),

        SUBSET_sum([No_Users_Impacted], subset([Rank month], 2)),

        0)

You can choose "show as percent" from the Edit Formula > Format options. For further conditional text formatting, see Edit Formula > Text, which allows you to style text based on the calculation results.

Arrow direction:

IF(

    SAFEDIV(

        (

            SUBSET_sum([No_Users_Impacted], subset([Rank month], 1)) -

            SUBSET_sum([No_Users_Impacted], subset([Rank month], 2))

        ),

        SUBSET_sum([No_Users_Impacted], subset([Rank month], 2)),

        0) 

> 0, "⇧", "⇩")

Arrow Colour:

IF(

    SAFEDIV(

        (

            SUBSET_sum([No_Users_Impacted], subset([Rank month], 1)) -

            SUBSET_sum([No_Users_Impacted], subset([Rank month], 2))

        ),

        SUBSET_sum([No_Users_Impacted], subset([Rank month], 2)),

        0) 

> 0, "red", "green")

#Sum of values on the last day in our time series:

SUBSET_SUM([Value], subset([Date],

SUBSET_MAX([Date])

))

#Previous day value:

SUBSET_SUM([Value], SUBSET([Date],

(DATEADD((SUBSET_MAX([Date])), -1, "day"))

))

#Sum of Values on the same day last month (compared to the latest day in the series):

SUBSET_SUM([Value], subset([Date],

DATEADD(SUBSET_MAX([Date]),(-1),"month")

))

#the "month" argument can be replaced with "year","week" or "day". If Max date is 22 May 2022 - the formula will retrieve a sum of values for the 22nd April 2022, or, in case of -1 Year, sum of values on the 22 May 2021.

# finding the value sum on the last day of the previous month :

SUBSET_SUM([Value], subset([Date],

 LASTDAYOFMONTH(

 (DATEADD(SUBSET_MAX([Date]),(-1),"month"))

)))

#Value sum for the latest Month - our month field is called [Month MMM-yy]:

SUBSET_SUM([Value], subset([Month MMM-yy],

SUBSET_MAX([Month MMM-yy])

))

# MoM difference (month on month) from the latest month in our datset:

SUBSET_SUM([Value], subset([Month MMM-yy],

SUBSET_MAX([Month MMM-yy])

))

-

SUBSET_SUM([Value], subset([Month MMM-yy],

DATEADD(SUBSET_MAX([Month MMM-yy]),-1,"month")

))

# Year to date Value sum (sum of all values for the latest year in the time series):

SUBSET_SUM([Value], subset([Year],

SUBSET_MAX([Year])

))

# Sum of values for the previous year:

SUBSET_SUM([Value], subset([Year],

DATEADD(SUBSET_MAX([Year]),-1,"year")

))

# YoY (year on year) difference (sum of Values for the Max year - previous year):

SUBSET_SUM([Value], subset([Year],

SUBSET_MAX([Year])

))

-

SUBSET_SUM([Value], subset([Year],

DATEADD(SUBSET_MAX([Year]),-1,"year")

))

#Difference from a previous day in a time series where [Value] field is a cumulative

SUBSET_MAX([Value])

-

SUBSET_MAX([Value], SUBSET([Date],

(DATEADD((SUBSET_MAX([Date])), -1, "day"))

))

#Increase rate (delta divided by the previous period) -in a time series where [Value] field is a cumulative :

(SUBSET_MAX([Value])

-

SUBSET_MAX([Value], SUBSET([Date],

(DATEADD((SUBSET_MAX([Date])), -1, "day"))

)))

/

SUBSET_MAX([Value], SUBSET([Date],

(DATEADD((SUBSET_MAX([Date])), -1, "day")

#Create a unicode arrow next to your result:

IF(

SUBSET_SUM([Market Yield],subset([Month rank],1))

-

SUBSET_SUM([Market Yield],subset([Month rank],2))

> 0, "⇧", "⇩")

# Colour the font or arrow depending on the calculation outcome (RGB colours can be used instead of words e.g. "rgb(255,0,0)" for red):

IF(

SUBSET_SUM([Market Yield],subset([Month rank],1))

-

SUBSET_SUM([Market Yield],subset([Month rank],2))

> 0, 

"green", "red")

Below you can find two demo files which contain multiple conditional formulas - you can import the IOZ projects to explore the content.