Smart KPI to compare last month vs this month / delta

Arcadia’s KPI visual type allows you to run comparison between two different measures. Let’s say you wanted to show this months user activity as the KPI but also show how it compares to last month (with colored arrows & percentage change).

image

You can read more about the KPI visual & its various settings here:
http://documentation.arcadiadata.com/latest/#pages/topics/visual-kpi.html

In the below example we will focus on simple example to compare activity week over week.

To perform the comparison we need to use SUM-IF statement. The IF statement will limit the SUM to the date range of interest. Here’s how to setup the expressions. We used “date_sub” to offset our time by the interval we are interested in:

This week measure:

   sum(if([Ts] > date_sub(now(), interval 1 week),1,0)) as 'this week'

Last week measure:

   sum(if([Ts] between date_sub(now(), interval 2 week) and date_sub(now(), interval 1 week),1,0)) as 'last week'

Then we add these expressions to the respective shelves:

5 Likes

Hi Shaun,

I have data like this:

1212

The average for calendarweek 5 is: 4,8
The average for calendarweek 6 is: 6

How can I transfer your example to my data?

Thank you
G

@Georgio you can use AVG-IF statement but use NULL for the false condition. Something like:

 avg(if([calendarweek]=6, [value], NULL)) as 'this week' 

That should calculate the correct average because it only considers non-null values.

3 Likes

Hi Shaun,

Thanks for the information. Also is there any way to use the max date instead of the now() function? when I use the max function I get: AnalysisException: aggregate function must not contain aggregate parameters

@Carlos_Bocanegra The “max date” is a calculation that cannot be done within the same operation as the KPI calculation.

You would need to derived the “max date” using one SQL and then join that back into the calculation of your KPI.

In this case I would recommend manually writing your own SQL and using dataset from SQL to build visuals.
http://documentation.arcadiadata.com/latest/#pages/topics/edit-dataset-from-query.html
This will allow you to write any complex SQL like CTE to derived the metric you are interested in.

2 Likes

Thank you will try it!