I'm looking to create data driven alerts on a large chart (spark line charts essentially) where each chart has an independent axis. For each chart I want to include the 14 day average or standard deviation for that particular chart. This average or std dev will vary by chart based on the volumes by each entity. The alert would be triggered once the most recent day's information breached the average or standard dev. I would only want the alert to trigger when the condition was met for the specific entity that triggered it, not the entire list of entities.
In the picture I have volumes pictured by day in a stacked bar format. The left axis is independent for each chart and the average is calculated. I would then want to trigger an alert each day when the total was > the average. So fo the most recent day, alerts would be triggered for Client 1 and Client 5.
While I could create a unique chart for each client and then related alert, this would be incredibly time consuming as I have over 50 unique clients. Ideally, within the alert email, I'd want to only include the clents that exceeded the average rather than the entire chart.
Any thoughts from the experts on this?
Do you have a small sample packaged workbook? I think the calculations sound possible with LOD calcs but a bit of playing with a workbook that approximates the data here would help.