9 Replies Latest reply on Oct 15, 2018 1:44 PM by Nathan Anderson

    Using Tableau for Conditional Alerting

    Nathan Anderson

      This is both a use-case, and a question about conditional alerting.

       

      I've recently taken to using Tableau for some conditional alerting for our clients. I've created a basic .twbx mockup which should be a great starting point to getting there.

       

      Basically, we have a number of factories across different clients, and our service depends on making sure those clients attach one of our serial codes on their factories before shipping out to retailers.

       

      We need to set up a notification system the notifies each individual client if there is a significant drop in the number of codes attached in a given week. One challenge is that I don't want to do a total threshold, or even a percent difference threshold because different factories have different dynamics. My solution is to setup a notification to the client if the week-to-week difference in number of codes created is less than one standard deviation below the mean for the factory (again, based on week granularity). And to also create a time-series view so that the client can log on to see the number of codes created in the factory.

       

      In creating the time series, I used reference lines, with upper and lower standard deviations, and color coded the weeks which have a drop in codes created. (Of course, I had to filter out the current week because incomplete data will always set off the alert, I did this with the following LOD expression:

       

      DATETRUNC('week', [Date], 'sunday') <

      { MAX(DATETRUNC('week', [Date], 'sunday')) }

       

      The result looks like this:

      screenshot_299.png

       

      The client is pretty happy with this, and internally, they pretty satisfied as well... but the next step is what I'm wondering about. I want to set up an alert on Monday if the previous weeks' data saw a significant drop in codes created.

       

      Looking at that Tableau view, you would expect:

      • Client 'a' to get notifications on April 30th and May 7th
      • Client "b" would get a notification on June 4
      • Client "c" on Apr 30th
      • Client "d" on Apr 23

       

      I've used Tableau's built-in notification functionality before, but I've noticed that this notification strategy will send out a notification if any of the conditions are true. My idea for a work around is to create another worksheet to set up the alert, but I would like to send the client a notification with a .jpg of just their factory with the trailing eight weeks or so.

       

      My first attempt was to try this LOD calculation. I think I might be using the LOD calcs wrong, and that's why this isn't working:

       

      IF [NOT CURRENT WEEK] = TRUE

          AND sum([Codes]) < sum([Codes]) - {FIXED [factory_name], DATETRUNC('week', [created_at], 'Sunday'): STDEV([Count])}

      THEN "ALERT"

      ELSE "Don't Alert"

      END

       

      I know this combines aggregations, and is not the right way to do this, hence my posting here.

       

      Thanks!

       

       

       

      If it's kosher to do so, wanted to tag my company's Tableau rep in this just to take a look Arlo Paranhos

        • 1. Re: Using Tableau for Conditional Alerting
          Peter Fakan

          Hi Nathan,

           

          I'm not very familiar with notifications but I've got a question about your approach; why don't you just create a separate viz for each factory and then set your notification trigger off these vizzes ?

           

          You can still have the combined dash that shows all of the factories, but the work effort involved seems a lot less exhausting than what you are trying to do now.

           

           

          HTH

           

          Peter

          • 2. Re: Using Tableau for Conditional Alerting
            Nathan Anderson

            That was the idea. To create a new worksheet for each client, that is hidden from uploading to our Tableau Server.

             

            My question was more about how to use a Standard deviation, aggregated by week, rather than a percentage change or a change in sum from week-to-week... because some client's factories only make dozens of product requiring serial codes per day, and some make thousands.

             

            I played with it last night, and here is my solution:

             

            • I created an alert flag using the following LOD expressions, including nested LOD Expressions:

             

            ALERT Flag:

            {FIXED [Factory], DATETRUNC('week' , [Date]) : sum([Codes])}    -

            {FIXED [Factory]  : STDEV(

                {FIXED  [Factory], DATETRUNC('week' , [Date]) : sum([Codes])})

            }

             

            I then used this calculated field to set the time-series graph to color:

            ALERT:

            ATTR(IF [ALERT Flag] < 0

                 THEN "ALERT"

                 ELSE "DONT ALERT"

            END)

             

            • I filter out current week using:

            ISCURRENTWEEK:

            IF DATETRUNC("week", [created_at], "Sunday") =

            DATETRUNC("week", TODAY(), "Sunday")

            THEN TRUE

            ELSE FALSE

            END

             

            • Then I duplicate for each factories (this might be a challenge as we scale up because we're set to have some 200 factories by the end of the year.

             

            • Then I filter out for last week:

            ISLASTWEEK:

            IF

                 ATTR(DATEPART('week' , [created_at], "Sunday")) =

                 ATTR(DATEPART('week' , TODAY(), "Sunday")) - 1

                 THEN TRUE

                 ELSE FALSE

            END

             

            • Set the filter to TRUE
            • Set up Alert : WHEN ALERT = 'ALERT'
            • 3. Re: Using Tableau for Conditional Alerting
              Matt Coles

              Building separate vizzes for each client, or better yet, customized views for each client, then creating separate alerts on each could work, but it doesn't scale if you've got a lot of clients. For conditional alerting and reporting (aka email bursting) that must be customized for each recipient, consider VizAlerts if you're using Tableau Server. It was built for this sort of work.

              • 4. Re: Using Tableau for Conditional Alerting
                Nathan Anderson

                I've considered that.... unfortunately I think the installation process is a little heavy on the networking aspect which I'm not so caught up on. We're a lean team - our data services team right now has three people, and I'm pretty much to only Tableau guy. Tableau doesn't offer product support for VizAlerts... would be a nice addition to the native features though.

                • 5. Re: Using Tableau for Conditional Alerting
                  Nathan Anderson

                  Wanted to update this with a new packaged workbook.

                   

                  This workbook is identical to the one I'm using for my company, only with fake data.

                   

                  In the bottom left, you will see what I've a worksheet called "Current Status". Essentially this worksheet is only showing data from last week.

                   

                  Once this is up on our Tableau Server, what I'd like to do is notify myself and my boss whenever one of these filled circles is red, notifying him which factories are offline - the ones that say "alert"

                   

                  How do I do this?

                   

                  https://public.tableau.com/static/images/Se/SerialCodesCreatedbyFactory-DummyData/FactoryRegistrationTimeSeries/1.png            

                  The worksheet should look like this:

                  screenshot_303.png

                  • 6. Re: Using Tableau for Conditional Alerting
                    Hala Tehini

                    Hi Nathan,

                     

                    I am currently having the same issue.

                    Have you managed to find a way to trigger an alert when alert="Alert" ?

                     

                    Thanks

                    Hala

                    • 7. Re: Using Tableau for Conditional Alerting
                      Michael Hesser

                      Hi Nathan;

                       

                      I don't know much about data-driven alerts, but would you have better luck if-- instead of using string values-- you use a continuous value axis (where 1 = Alert and 0 = Don't Alert) ?

                       

                      Good luck!

                      • 8. Re: Using Tableau for Conditional Alerting
                        Matt Coles

                        Michael is exactly right. I just explained how to do this in another thread:

                         

                        https://community.tableau.com/thread/283956

                         

                        Besides forcing a 1/0 return value, the other trick is to use filters or logic in your calc to filter out any data you don't want to be alerted about (past values, for example). If your dash only ever shows currently alertable values, though, then you don't have to worry about that.

                        • 9. Re: Using Tableau for Conditional Alerting
                          Nathan Anderson

                          "If your dash only ever shows currently alertable values, though, then you don't have to worry about that."

                           

                          That's the idea.