Metrics for Key Risk Indicators (KRI) breaches

[dmakan]

We track KRI breaches across 8 different countries.  My data looks as follows and covers a 12 month period:

Note:  A "KRI ID" can be the same for different countries

I have added a column to flag:

• A breach (1 if breach and 0 if not)

How do I add a column to show number of breaches for a certain KRI for the 12 month period?

• “3 in 6 month” breaches (sum of breaches per KRI)
• Which “3 in 6 month” KRI breaches are new (was previously “2 in 6 months” and is now “3 in 6 months”)
• Which KRI’s that were breaching “3 in 6 months” have been resolved (was previously “3 in 6 months” and is now “2 in 6 months”)

With this i will able to give a view of my KRI’s that are breaching as well as 3 in 6 month breaches (Consistent breaches) which we measure as a key metric against each countries KRI’s

I also need to create a “6 in 12 month breach” flag

[Anonymous]

Hi @dmakan ,

I don't quite understand the logic of your column:

Especially here:

Why is 0,1,0,1,1?

Best Regards,
Dino Tao

[dmakan]

Hi Dino

Thanks for taking the time to look through my query. 

So for the column "Breach"  my condition merely assigns a 1 if "Red" or "Yellow" and a 0 if "Green".  I use this to count the number of breaches per country by summing the field when displaying the metric for breaches on a card.

My apologies i see i made an error on the sample data.  Should reflect as per below.  (Also you can ignore the red and yellow thresholds - only inculuded for expanation of the breach)

The yellow column is where i flag a KRI as breaching 3 or more times in the last 6 months.  

What i need asisstance with (Blue Column) is identifying which:

1.  KRI's have improved from 3 or more breaches in the last 6 months (Breach resolved from 3 in 6 months)

2.  KRI's have started breaching 3 or more times in the last 6 months (New breach 3 in 6 months)

Note: I filter on the latest Value date to display the current KRI metrics for the country, however the data has to be pulled for the last 12 months to be able to identify these "3 in 6 month breaches" and "6 in 12 month breaches.

Hope this helps.  Thanks