Send Alerts based on metric value change daily or weekly

Hi, I am exploring Looker alerts feature and I am just wondering if there’s an easy way to setup alert based on some threshold of daily or weekly change of the metrics of interest.

Let say I have something like the below:

I understand that you can put filters for your alert when you setup the schedule, but I just don’t know how you can include that table calculation as a condition in the filter.

Is this something that is possible to be done?

thanks in advance!

Hey @Glenn.Wawo,

This should be totally possible. In this case, you may want to (1) use a filter to create a where or a having in the query in question such that it has no results unless your condition is met; (2) in the scheduled look choose the options that only send out the Look when there are results and those results have changed since the last time the Look was sent. In your case it could be another table calculation with IF(condition) then yes/no.

Best,

Sasha

Hi,

As Sasha says above, you will need to have a filter which acts on the weekly change to produce results only if that condition is breached. Then you can schedule reports to send if and only if there are results breaching that condition.

Unfortunately its not possible to filter on a table calculation, you can write custom filters, but you cannot use offset/pivot_offset - table calculations happen on the results of the query, whereas filters happen inside the query, so there’s no possibility to use offset/index etc.

A solution I could see if you you to create the WoW field inside the model itself, for that you would need to create measures where Total Adjustment Value is filtered to this week and last week only, and then create a new measure which calculates the difference, something like:

measure: total_adjustment_value_this_week {
    type: sum
    sql: ${adjustment_value}
    filters: {
        field: is_this_week
        value: yes
    }
}

 measure: total_adjustment_value_last_week {
    type: sum
    sql: ${adjustment_value}
    filters: {
        field: is_last_week
        value: yes
    }
}

 measure: total_adjustment_value_wow {
    type: number
    sql: ${total_adjustment_value_this_week}/NULLIF(${total_adjustment_value_last_week},0) - 1 ;;
    value_format_name: pct_2
}

You’ll need to create two dimensions to determine if its this week or last week. Then you will be able to create a having clause on this measure and set a schedule based on that.

Hope this helps,
Andy

Hi Sasha,

Thanks for the reply. This would be ideal yes, but I don’t know how can I set this condition in the filter (let say: for today’s day of week, if change WoW > 5%)

To my knowledge you require pivot_index to calculate that and I cannot really use the formula within the filter. Do you know alternative work around using filters?

cheers
Glenn

Hi Andy,

I was thinking about this solution before, thanks. However, I am trying to avoid using this method because there will be a lot of different metrics that will need to be setup with alerts.

This method will force me to create a lot of additional fields for many different metrics just for the sake of setting up an alert which is not ideal.

Hey @Glenn.Wawo,

What I was suggesting was to create one more table calculation with your if(condition) that returns yes/no based on your data for particular day. change WoW > 5% In your schedule then you tick the box to send reports if results have changed since last run. Should you have more questions feel free to pop on chat.

Best,

Sasha