6 Replies Latest reply on Feb 23, 2016 1:50 AM by Christoph Kreibich Branched from an earlier discussion.

    Alerting for day to day changes with dynamic thresholds?

    Christoph Kreibich

      Great stuff, thanks for sharing!


      I already used your solution, Matt. Currently I am building a dashboard for a lot of different items we take out a stock where I want to use alerts based on the relative changes from one day to the other as the quantities we use vary heavily. I have a hard time figuring the right table calculation out. Initially, I thought replacing the SUM by the COUNT of my parts would be enough...


          WINDOW_SUM(COUNT[TYPE],FIRST(),0) >= MAX([Threshold Mail])

              AND WINDOW_SUM(COUNT[TYPE],FIRST(),0) < MAX([Threshold Mail]) ,

          MAX([TS]) ,




      To make it even more complicated I want to use different thresholds for different items (e.g., for item 1 I need a alert when I have relative changes of 1%, whereas with other items I don't need an alert even if I have a relative change of 90%). Any suggestions for my two problems?


      Thanks for helping!

        • 1. Re: Alerting for day to day changes with dynamic thresholds?
          Matt Coles

          Hey Christoph--let me think about it and get back to you..

          • 2. Re: Alerting for day to day changes with dynamic thresholds?
            Matt Coles

            Hey Christoph, I broke this out into a new question so that it could be addressed separately from the other thread.

            • 4. Re: Alerting for day to day changes with dynamic thresholds?
              Matt Coles

              FYI, this is still on my radar but I haven't had a chance to play with it--hopefully sometime this week.

              • 5. Re: Alerting for day to day changes with dynamic thresholds?
                Matt Coles

                Okay, so I took a stab...I'm not entirely sure I'm getting to the level of sophistication you are after, but hopefully this demonstrates enough that it might help get you going anyway.


                So the attached workbook is based on Superstore sales. I know you're looking for daily, but the data in Superstore makes more sense on a monthly basis, so that's how I built this. It's also locked in time so the relative date ranges I use are longer than they'd be in real life, just so I have some data to show.


                The first sheet shows a simple sum of sales over time, broken out by US State. The goal being to take this and get to the point of being able to alert if the sum of sales difference for a specific state exceeds its own particular threshold. Hopefully that's close enough to what you're trying to do that it's helpful...


                The second sheet just converts the sum of sales measure to a quick table calc showing the difference of the sum of sales between months. I double-clicked the pill and stole the calc because I generally suck at table calculations (I'll get better someday, I promise).


                The third sheet contains a "Sales Diff" calc which is just that same formula I stole from the second. I set a relative date filter against the data, intended to limit it to the last two time units (months in this case, and 16 months in the workbook since as of right now, that's when the Superstore data ends). There's also a filter set on the "Sales Diff" value to only show drops in sales on a month-over-month basis (max of 0). So this would effectively function as an alert that says, "each month, email me if one or more states have a drop in sales compared to the previous month".


                The fourth sheet makes the thresholds (currently just "0") customizable at the state level. I created a new calc called "Dynamic Alert Filter" that calls out four specific US States and compares their month-over-month sales to individual Parameters that can be adjusted for each. I then use that as a filter rather than a simple one on the "Sales Diff" calc. The effect is now that the alert we built in the third sheet can be adjusted for each state. For example, if Washington typically has more sales revenue than Alabama, we can adjust the threshold lower, so that we don't get alerted unless Washington's sales drop is more than $9000, whereas we get alerted if Alabama's sales drop is more than $1000.



                So, that's how you might go about doing it. But it's worth mentioning that we're hardly limited to this! We can get pretty darn crazy with our filter criteria. The thresholds for each state do not need to remain static Parameter values. They could be derived from any other calc we might think up, or even potentially a blended datasource. Maybe instead of having to decide on each threshold per state, you could write a Level of Detail calculation that found the standard deviation for the sum of sales for the state, and base your threshold value on that. It's up to you!


                Let me know if that's not hitting the mark for you. If not, probably a mockup workbook would help me best understand what you're trying to accomplish.

                • 6. Re: Alerting for day to day changes with dynamic thresholds?
                  Christoph Kreibich

                  Hi Matt,


                  thanks for the help and sorry for the late reply! I will check it later today or tomorrow!