14 Replies Latest reply on Aug 21, 2018 11:29 AM by tadd.wagner

    Executive Level Alert Dashboard

    tadd.wagner

      Hi all,

       

      I am trying to recreate/modify a neat dashboard that I found here: Tableau 201: 3 Ways to Add Alerts to Your Dashboards

       

      I have been struggling the past 8 hours on getting this setup. I can easily do this in Excel, but I'm still fairly new to Tableau. I would love to have it in Tableau so that this would update automatically every week.

       

      See below:

      Above the purple line, this is what I currently have in Tableau (took me longer than it should have to get here, but hey, I'm learning! )

      Below the purple line is a mock-up of what I would like to have for my dashboard, where the green means this week is really good in comparison the the average of the past 12 weeks and red is not good.

      Below that is a screenshot from the link I have been referencing (shared above). Any suggestions on how to get past what I have above the purple line to my final product?

      Our data comes in daily, labeled as "Invoice Date."

       

       

        • 1. Re: Executive Level Alert Dashboard
          Ankit Patel

          Hi Tadd,

           

          This is very easy to put together in Tableau. You need to create a calculated field to define your alert condition. Once you have that you can put the view together by selecting Mark as Circle and dropping the condition calc to Color mark card and metric pill to Label mark card.

           

          See below sample from Superstore - I have attached the workbook as well if you want to follow along on how it was created (workbook is in version 10.5)

           

          1 of 1 people found this helpful
          • 2. Re: Executive Level Alert Dashboard
            tadd.wagner

            Hi Ankit,

             

            This is very helpful. However, I think I left out one detail that has been causing me issues. In the calculated field, I want to set it up so that alerts are based on % change. So if this week is down 20%+ then it's a bad alert. If it's up 20%+ (compared to previous 12 weeks), then it's a good alert. Does that make sense? Thank you for your help!

            • 3. Re: Executive Level Alert Dashboard
              Ankit Patel

              Hi Tadd - are you referring to Data-Driven Alert from Tableau Server -- Send Data-Driven Alerts from Tableau Online or Tableau Server

              This can be achieved but a view has to have continuous numeric axis in order to create Alert on Tableau Server.

               

              From what you are describing "In the calculated field, I want to set it up so that alerts are based on % change. So if this week is down 20%+ then it's a bad alert. If it's up 20%+ (compared to previous 12 weeks), then it's a good alert" this will/should automatically change your view as data changes every week based on the calc threshold you have setup once.

               

              Hope I am making sense.

              • 4. Re: Executive Level Alert Dashboard
                tadd.wagner

                Ankit, I do not think this is what I'm looking for.

                 

                I want to do what you referenced and showed in your attached workbook. However, I want the "bad alert" or "good alert" to be based on a % difference/change, not just whether or not the sales were above $10,000.

                 

                I know this doesn't work, but the logic I'm looking for is like this:

                 

                (Sales for Previous Week - AVG Sales for Prior 12 Weeks) / (AVG Sales for Prior 12 Weeks).

                 

                So if Customer B had $150 sales on Product #1 previous week, and $100 average over prior 12 weeks, this would be a 50% increase which could be greater than the 20% I'm looking to mark high enough change to draw an alert.

                 

                Does this make sense? Thank you, again.

                • 5. Re: Executive Level Alert Dashboard
                  jehan siti aisyah

                  HI Ankit,

                  I want to open your workbook but I can't because I'm using Taleau 10.4.

                  What should I do?

                  • 6. Re: Executive Level Alert Dashboard
                    Paulo Dantas

                    Hello jehan,

                     

                    try to install a new Tableau Desktop.

                     

                    Cheers.

                    • 7. Re: Executive Level Alert Dashboard
                      Vladimir Khvan

                      Hi Tadd,

                       

                      Why can't you apply this logic for your calculated field from that example? And then put it to Color adjusting colors for each mark as red and green

                       

                      IF [(Sales for Previous Week - AVG Sales for Prior 12 Weeks) / (AVG Sales for Prior 12 Weeks)] <= -0.8 THEN “Bad Alert”

                      ELSEIF [(Sales for Previous Week - AVG Sales for Prior 12 Weeks) / (AVG Sales for Prior 12 Weeks)] >= 0.2 THEN “Good Alert”

                      ELSE “No Attn Needed”

                      END

                       

                       

                      • 8. Re: Executive Level Alert Dashboard
                        Vladimir Khvan

                        Sorry, misprint )) "-0.2"

                         

                        IF [(Sales for Previous Week - AVG Sales for Prior 12 Weeks) / (AVG Sales for Prior 12 Weeks)] <= -0.2 THEN “Bad Alert”

                        ELSEIF [(Sales for Previous Week - AVG Sales for Prior 12 Weeks) / (AVG Sales for Prior 12 Weeks)] >= 0.2 THEN “Good Alert”

                        ELSE “No Attn Needed”

                        END

                         

                         

                        • 9. Re: Executive Level Alert Dashboard
                          Ankit Patel

                          Hi Jehan,

                           

                          I normally keep 2 versions of Tableau Desktop's installed on my PC - one version is "Corporate" version - i.e. lets say 10.5.x which is same for Tableau Server 10.5.x that you have in your Corporate environment.

                          Another version installed on my PC is the latest one from Tableau i.e. 2018.2

                           

                          The trick here is having Default File Open to correct version is to follow series of install steps - the latest install will take over as Default version of Tableau Desktop which in turn will open any saved Tableau files on the PC (.twb, .twbx, .tds. .hyper, .tde, etc)

                           

                          Steps:

                          1. if you already have a version installed i.e. 10.5 - then download and Install the latest 2018.2 version (do not remove the 10.5 version)

                          2. now that you have the latest version 2018.2 - download and install your existing version 10.5.x (no need to remove the old one - the new 10.5.x will overwrite the old 10.5.x and make it Default Program to open for above mentioned files)

                           

                          Now you can download any workbook from Tableau Public or Forum and open it the latest version 2018.2 manually without any issues.

                          1 of 1 people found this helpful
                          • 10. Re: Executive Level Alert Dashboard
                            Ankit Patel

                            Tadd - With Valdimir's formula you can achieve the desired output - have you tried it?

                            • 11. Re: Executive Level Alert Dashboard
                              tadd.wagner

                              Hi Ankit. I am not able to easily call on "Sales for Previous Week" or "AVG Sales for Prior 12 Weeks." These are both within a dimension I have named "Week type for comparison."  I have the calculation for that here:

                               

                               

                              So maybe I can edit Vladimir's formula (thanks for that by the way, Vladimir ) to somehow call on "prior 12 weeks" or "Previous week" from within this dimension.

                               

                              I know this would be much more helpful if I could set this up in an example workbook using the superstore database. Maybe I'll work on doing that this afternoon.

                               

                              For now, this is where I am (with product names and customers hidden, of course)...

                               

                              There remain three issues:

                              1) The alert formula is not in place (as we've already discussed). What you are seeing is red for anything negative % change and green for anything positive % change.

                              2) The total column is all grey, it's not applying the colors at all.

                              3) My calculation behind the scenes for average of previous 12 weeks is looking at the last 12 weeks and if there are 4 weeks with no sales for a customer, it divides by 8 for average, instead of the 12. This makes some of the alerts to be incorrect since it is comparing the previous week's sales to an inaccurate average of previous 12 weeks. For example, customer 2 Product 1 should actually be a green circle, not red.

                                   a) I have tried fixing this by adding in the following. This does add in 0's where it was previously null. however, this then messes up my table calculation that allows me to calculate the % difference needed for the alert circles.

                               

                              • 12. Re: Executive Level Alert Dashboard
                                tadd.wagner

                                Well. Found the answer to issue #2 here: Re: How to enable color code on totals?

                                 

                                That was easy...can't believe I didn't see that!

                                • 13. Re: Executive Level Alert Dashboard
                                  tadd.wagner

                                  Thanks Vladimir,

                                   

                                  In addition to the notes I have added on most recent comments, I have tried this and it says "Can't subtract boolean value from boolean value"