14 Replies Latest reply on Jul 30, 2016 5:11 PM by Veronica Simoes Branched to a new discussion.

    Example: Alert when a Sales Target is achieved

    Matt Coles

      Rithesh had asked in Re: What alert do you want? I'll build it at TC15! whether VizAlerts could send an email out when a sum of the sales met some threshold. The answer is yes. This post shows the proof-of-concept.

       

      First, you could simply build a viz where you filter against the SUM([Sales Amount]). Once the threshold is reached, the data will show up in your viz, and you'd get an email. But the tricky thing about these threshold alerts where the amounts only ever increase is that you would then continue to get alerts each time VizAlerts ran against your alert thereafter. If you're sending a big email out, you don't really want that!

       

      To avoid that situation, your data must include the date and time of each sale, and how much the sale amount was. Using that information, you can construct a relative date filter against a series of table calculations that allows your viz to logically say "Show me the earliest date and time we hit our sales target, but only if it happened in the last N minutes". This way, once the next time your alert fires, that date has moved outside of your relative date window, and no subsequent alerts are sent.

       

      This can also work for sales data that only includes the date of the sales and not the date and time , but you would be limited to a daily alert at that point--and typically you wouldn't want to wait an entire day to find out that you achieved a sales target.

       

      The essential steps are outlined in the twbx I attached. Just go one sheet at a time to see the progression. After publishing it, you'd subscribe to an Hourly alerts schedule.

       

      General Steps:

       

      1. Create calc for running sum of sales

      2. Create a parameter denoting the sales target amount

      3. Create a calc that returns t/f for whether sales target was achieved

      4. Create a calc showing the minimum date where the sales target was achieved

      5. Do a silly calc to find the number of minutes since the minimum sales target achieved date occurred (there must be a more elegant way that avoids this, but I couldn't figure a better way out)

      6. Turn the number of "minutes since sales target achieved" back into a date with another calc--this represents the date that the sales target was achieved

      7. Set your relative filter on this last calculation for 60 minutes

      8. Publish to Server

      9. Subscribe to the last sheet on the Hourly alerts subscription schedule

       

      Ta-da!

        • 1. Re: Example: Alert when a Sales Target is achieved
          Rithesh Makkena

          Bravo, great solution. I been using window script all these days for each and every alert, where one report will send out CSV file with 1 or 0 and next alert will run only if value is 1. But with this solution i don't have to maintain different scripts for and every report/alert.

          • 2. Re: Example: Alert when a Sales Target is achieved
            Matt Coles

            Just a follow-up to this. Wilson Po is a table calc genius and came up with the following solution that accomplishes the same thing, but without all the silly calculations I had done myself.

             

            Steps for the attached workbook:

             

            1. Create a parameter denoting the sales target amount

            2. Create a table calc that returns the date sales target was achieved, or NULL for all rows where it wasn't

            3. Filter against the calc in (2), and exclude NULLs

            4. Set your relative filter on this last calculation for 60 minutes

            5. Publish to Server

            6. Subscribe to the last sheet on the Hourly alerts subscription schedule

             

             

            I do want to add one more bit of caution to both solutions, regarding ETL lag. Using the relative date trick assumes that your data will be present and accessible in your data source instantaneously. It also requires that the timestamps in your sales data represent the exact time that the sale became recognizeable. It further assumes that VizAlerts will always run your alert exactly on time, as well.

             

            If you are pulling down not-quite-live data, or basing your alert on extracted data, you will need to get a little fancier than a simple relative date. You would probably want to use a lower and upper date bound, where you accounted for the amount of time it takes for data to make it from your operational system (e.g., Salesforce) to your reporting data. So if you collected data every 15 minutes from SFDC, you might want to filter your date based on the date being older than one hour and 15 minutes, and no more recent than 15 minutes ago. That way, you allow for some lag time for the data to become present.

            4 of 4 people found this helpful
            • 3. Re: Example: Alert when a Sales Target is achieved
              Mitch Haus

              I am trying to solve a similar issue, but without using a running table calculation, and only isolating monthly sales.

               

              I want to take all of our active sales people, and find not only which ones have had a month in the last 3 months that is over a threshold in sales, and isolate only the sales people who have never had a month breach this threshold ever in the past; this is were I am having problems...

               

              I have parameters for a start date and end date that I want to pull sales recognition for (set at the last 3 months for now). I also have my Sales Threshold parameter which is set at $10,000 currently.  I created a True/False measure that labels every month as True if the sales for the month was over the threshold, and False if not; I then used this to filter out all False dates for each sales person.

               

              The point I am stuck at is I need to pull ONLY the earliest month of achievement for each person, and then use the Start Date & End Date parameters to eliminate anyone whose date of first achievement is prior to the period...  Any guidance would be greatly appreciated... I unfortunately can't post the data source due to confidentiality issues, but there is a screenshot below of where I have gotten to.  I am trying to avoid Table Calcs since this will be resorted, thresholds changed, etc. and the data needs to be dependable.

               

              Sales Recognition.JPG

              • 4. Re: Example: Alert when a Sales Target is achieved
                Matt Coles

                This problem looks like a fun one...let me noodle on this and see what I can come up with!

                • 5. Re: Example: Alert when a Sales Target is achieved
                  Matt Coles

                  So, I can't think of a way to do this except for the following:

                   

                  1. Table calcs (I know you said you don't want to do this, though)

                  2. Custom SQL (assuming this is against a sql database of some kind). Since you're using parameters, the query is pretty simple. Use your standard connection, then add Custom SQL as a new table that left joins to the Consultant ID, and runs the following query:

                   

                  SELECT [Consultant Id]
                  FROM [Consultants] c
                  WHERE NOT EXISTS (
                      SELECT 1
                      FROM [Sales] s
                      WHERE s.[Consultant Id] = c.[Consultant Id]
                          AND s.[Order Date] < <Parameters.Start_Date>
                      GROUP BY CAST(YEAR(s.[Order Date]) AS char(4)) + CAST(MONTH(s.[Order Date]) AS char(2))
                      HAVING SUM(s.Amount) > <Parameters.Sales_Target> 
                  )
                  
                  

                   

                  (this is the SQL Server version)

                   

                  The logic is: Return a set of all Consultant Ids for consultants whose sales have never exceeded the Sales Target parameter value in a one or more calendar months, when the transactions themselves occurred before the Start Date parameter.

                   

                  Not sure if that's a workable solution for you, but it's a solution. The rest of the workbook is simple, you're just filtering against the sum of sales when the sales took place in the current month, then just keep null Consultant Ids from the CustomSQL query.

                   

                  Does that help?

                  2 of 2 people found this helpful
                  • 6. Re: Example: Alert when a Sales Target is achieved
                    Mitch Haus

                    That actually helps a lot- I kind of thought that we would have to do something like that on the front end query that would do this filtering for me.  I attempted to use LOD expressions to isolate the minimum month and year of the first month of hitting the threshold, which DID properly filter everyone down to only their first date of accomplishment, and it updated correctly when I updated the threshold, but for some reason the sales it gives me for each person is a SUM() of all their months' sales that reach the threshold rather than just the sales for that first month...

                     

                    I probably will have to go with the SQL query.. Thanks for your help and quick response!

                    • 7. Re: Example: Alert when a Sales Target is achieved
                      Matt Coles

                      Glad to hear it! But hold on--I think there's another solution with nested LOD calcs (God I love them) that can work in a more elegant fashion here...

                       

                      If we create a single field that combines year and month, it gives us a way to get the sum of sales for each rep and month:

                       

                      Order YearMonth:

                      STR(YEAR([Order Date])) + STR(MONTH([Order Date]))
                      

                       

                      Now we can use an LOD calc to get the sum of sales for each rep and month. You can do this with a normal calc as well, but you'd have to obtain it via the Canvas in desktop, with Order YearMonth on there. A fixed LOD allows us to set our own level of detail and get a static value, regardless of what is on the screen:

                       

                      Sum of Prior Sales Per Rep and Month:

                      { FIXED [Sales Rep],[Order YearMonth] : 
                          SUM(
                              IF [Order Date] < [Start Date] THEN [Sales] ELSE 0 END // we only want to consider sales prior to the start parameter
                          )
                      }
                      

                       

                      Now for the magic. We want the maximum of these values for each rep. So that's another LOD:

                       

                      Max Prior Sales per Rep and Month:

                      { FIXED [Sales Rep]: MAX([Sum of Prior Sales Per Rep and Month]) }
                      

                       

                      So now we've got the value we want, as a new field associated with each Sales Rep, that will stay the same no matter how we've rendered things on the canvas, or our level of detail there. The only step remaining would be to filter against it such that we include only records where this amount was less than your Sales Target of 10,000.

                       

                      Does that work as a solution?

                      1 of 1 people found this helpful
                      • 8. Re: Example: Alert when a Sales Target is achieved
                        Mitch Haus

                        WOW- this seems to have worked perfectly! My issue was that I was using the LOD calcs to find a MIN() date that had Sales > the threshold rather than using the LOD to find Sales > the threshold.  I had done { FIXED [Consultant ID] : MIN([Order Date over Threshold]) } and this was always returning me the correct dates, but a sum of all the sales across every date that completed the threshold for that sales person.

                         

                        Thanks for your help!

                         

                        Mitch

                        1 of 1 people found this helpful
                        • 10. Re: Example: Alert when a Sales Target is achieved
                          Shawn Wallwork

                          Matt, is there a way to adapt this to trigger a sound file, instead of sending an email?

                           

                          Thanks,

                           

                          --Shawn

                          • 11. Re: Example: Alert when a Sales Target is achieved
                            Matt Coles

                            Like, on the computer of whoever subscribed to the alert? No, but if we're just talking a couple of people, you could have them set up an Outlook rule to do so based on a particular alert email that is sent. Or if it's a big room with speakers, you can set up an always-on computer with an email account that does the same, and just broadcast the sound over the speakers.

                            3 of 3 people found this helpful
                            • 12. Re: Example: Alert when a Sales Target is achieved
                              Mitch Haus

                              I actually built a macro last year in excel that data scraped auction.com listings for commercial real estate, and it would push out notifications based on parameters I set.  You could do the same thing if you link up your data warehouse with Excel and build a macro that is triggered every time a certain cell's value is refreshed and have sound play when a threshold is reached.

                               

                              Unfortunately, Tableau is not the program you would want to use for this sort of thing.  If you choose to go the Excel Macro route, I highly recommend the Visual Basic forums over at mrexcel.com

                               

                              Good luck,

                               

                              Mitch

                              • 13. Re: Example: Alert when a Sales Target is achieved
                                Shawn Wallwork

                                Ha! The second one is what my client is looking for. I'll pass it along. Thanks for this!

                                 

                                Cheers,

                                 

                                --Shawn

                                • 14. Re: Example: Alert when a Sales Target is achieved
                                  Veronica Simoes

                                  Amazing Matt

                                   

                                  Congratulations