Showing the evolution of a KPI over time in a calendar

[#1]

Hello Community,

I always wanted to create a simple but still very figurative way to provide a quick overwiew on how a certain KPI evoles over time and indicate if there is a correlation between performance and dates (i.e. Are Mondays prior to Wednesdays? Is the performance better at the start of months then towards the end?)

I approached this by creating a calendar. This is the final product:

The calendar is created by using a pivot chart with three dimensions:

weekday, month and week

To display the individual dates i used the formula =Day(Only({1<Calendar.FiscalYearsFromNow = {0}>} Date))

The Set analysis selects all values within our current fiscal year. FiscalYearsFromNow is generated in our master calendar and goes from 0 (current fy) to x (x fiscal years past). If you want to know more about how this is done, I suggest that you read the article on Relative Calendar Fields by HIC‌.

The highlighting is done by a formula in the background-color that compares two values (usually the actual value vs. the planned value):

if(Sum({1<Calendar.FiscalYearsFromNow = {0}>} RevenuePerHour) >  Sum({1<Calendar.FiscalYearsFromNow = {0}>} PlannedRevenuePerHour)   ,$(v_KA_Farbe1), // v_KA_Farbe1 = rgb(215,215,215)

if(Sum({1<Calendar.FiscalYearsFromNow = {0}>} RevenuePerHour) >  Sum({1<Calendar.FiscalYearsFromNow = {0}>} PlannedRevenuePerHour) * 0.95  ,$(v_KA_Farbe2),

if(Sum({1<Calendar.FiscalYearsFromNow = {0}>} RevenuePerHour) >  Sum({1<Calendar.FiscalYearsFromNow = {0}>} PlannedRevenuePerHour) * 0.9  ,$(v_KA_Farbe3),

if(Sum({1<Calendar.FiscalYearsFromNow = {0}>} RevenuePerHour) >  Sum({1<Calendar.FiscalYearsFromNow = {0}>} PlannedRevenuePerHour) * 0.85  ,$(v_KA_Farbe4),

if(Sum({1<Calendar.FiscalYearsFromNow = {0}>} RevenuePerHour) >  Sum({1<Calendar.FiscalYearsFromNow = {0}>} PlannedRevenuePerHour) * 0.8  ,$(v_KA_Farbe5),

if(Sum({1<Calendar.FiscalYearsFromNow = {0}>} RevenuePerHour) <=  Sum({1<Calendar.FiscalYearsFromNow = {0}>} PlannedRevenuePerHour) * 0.95  ,$(v_KA_Farbe6),

$(v_KA_Farbe0)

))))))

Note that this example will always show the same values, since the formula fetches the entire data.

If you want the KPI being related to the selections, obviusly simply replace the 1 with the $. But this will only highlight the calendarfields, that are included in the data selected. To display the entire fiscal year highlighted make sure to ignore all Calendar Selections.

For example

if(Sum({$<Calendar.FiscalYearsFromNow = {0},

               Calendar.Date  =,

               Calendar.Week  =,

               Calendar.Month  =,

               Calendar.Year  =,

               // ... whatever you allow the user to select

     >} RevenuePerHour) >

Sum({$<Calendar.FiscalYearsFromNow = {0},

    Calendar.Date  =,

               Calendar.Week  =,

               Calendar.Month  =,

               Calendar.Year  =,

               // ... whatever you allow the user to select

>} PlannedRevenuePerHour)   ,$(v_KA_Farbe1),

You see.. the last formula is a bit odd, since it gets quite long. But this is probably the one you want, thus taking selected dimensions into account, but still highlighting the entire calendar.

Since you repeat the same formula you might want to use variables for it.

if($(v_RevenueOverFY) > $(v_RevenuePlannedOverFY)  * $(v_KA_W1)   ,$(v_KA_Farbe1),

if($(v_RevenueOverFY) > $(v_RevenuePlannedOverFY) * $(v_KA_W2)  ,$(v_KA_Farbe2),

if($(v_RevenueOverFY) > $(v_RevenuePlannedOverFY) * $(v_KA_W3)    ,$(v_KA_Farbe3),

if($(v_RevenueOverFY) > $(v_RevenuePlannedOverFY) * $(v_KA_W4)    ,$(v_KA_Farbe4),

if($(v_RevenueOverFY) > $(v_RevenuePlannedOverFY) * $(v_KA_W5)    ,$(v_KA_Farbe5),

if($(v_RevenueOverFY) <= $(v_RevenuePlannedOverFY) * $(v_KA_W5)  ,$(v_KA_Farbe6),

$(v_KA_Farbe0)

))))))

Well, this works, and i like this way of displaying information. Im just not too sure wether this is a very good approach regarding performance and simplicity. So any feedback and suggestions are very welcome

Best regards,

Hannes