7 Replies Latest reply on Mar 23, 2017 6:18 AM by Simon Runc

    Dynamic Highlights using Formula

    Stacy Hennig



      I am trying to create highlight action that works off of a formula. I have trended spend at the UPC level for each week ending date. It is clear in the data when a particular UPC is on deal as there is a spike in sales. I would like to have an understanding based on the purchase cycle for an item when are the likely dates that those shoppers that bought on promotion would return to the store to purchase again.


      My thought would be if I could highlight between the start and end of the promotion dates that my formula would take the start date + purchase cycle and highlight all the way to the end date + purchase cycle. And I would like to see every one of their purchase cycles throughout the rest of the year.


      I have taken a picture of what I am trying to do below. Please let me know if this is feasible.




      Action Highlights Based on Formula.png

        • 1. Re: Dynamic Highlights using Formula
          Simon Runc

          hi Stacy,


          Are you able to post a workbook with some example data? I have looked at this kind of problem before, although done all the processing outside Tableau. We work in the UK grocery retail industry where "Promo Flags" in the data is, generally, awful...so have looked at generating our own promotional flags based on a change in RoS [sale spike] and change in Average Price, as well as structuring the data along "known" promotional cycles [eg. if we know a retailer generally runs promotions from Wednesday to Tuesday we'll set up our weeks to start on a Wednesday]). As we're trying to pick up promotional flags across 30k+ disparate products (cotton buds to champagne to bleach!!) we needed multiple models (eg. don't want to pick up ice-cream as being on promotion on a warm day, where there'll be a sales spike!), and also there are products which aren't promoted. In the end, we (that's the "royal we"...our stats/machine-learning genius actually did it!!) did the processing in Python...and was pretty complicated. As such I'd need to see how much "processed" data you have, and how much you need "worked out" in Tableau (eg. do you have the promo start date? do you have a field for the purchase cycle...etc.), and also the "rule" for what constitutes a "sales spike"


          If you can post some example data I'd be happy to take a look (no promises on a solution!!)

          • 2. Re: Dynamic Highlights using Formula
            Stacy Hennig

            Hi Simon--


            Thank you for reaching out, and I'm sorry it has taken me so long to get back to you. I'm attaching some cleaned up sample data. Unfortunately in this data set we also don't get promotional flags, but based on the customer it is very clear when a promotion was run, similar to the methodology you referenced on a change in RoS. Avg Price is not going to be a good indicator in this data because "them" is doing a BOGO deal which isn't being reflected in the data. Everything is at a weekly view so we are constrained to use the start date based on either the beginning (Sunday) or end of the week (Saturday). There hasn't been much data processing at this point, more of just an export of raw data from the customer's shopper card system. Purchase cycle for these 2 items is every ~16 weeks or 115 days.


            In the workbook I sent over, you will notice "us" has a promotional gap where all of a sudden "them" takes over. My theory was that "them" captured the customer when they took advantage of the customer's purchase cycle. Obviously, some people are brand loyal and won't change, but I'm more concerned about those that are deal reliant and switched.


            Please let me know if this provides enough context for you, and thank you for your help in advance.


            Kind Regards,




            http://C\Users\shennig\Desktop\Tableau\Dynamic Highlights Using Formulas Example Data 20170313.csv/

            • 3. Re: Dynamic Highlights using Formula
              Simon Runc

              hi Stacy,


              So I think it might take a couple of attempts for me to fully understand what you are trying to get to...so attached is a starter for 10! All I've done here is create a promo flag, based on the Spend.


              So I created the following calc...

              [Spend Percentile - Selected]

              WINDOW_PERCENTILE(SUM([Spend]),[Promo Tollerance])


              where promo tolerance is a parameter to select the Nth percentile....I've currently set this to 0.7 (or the 70th percentile), which looks to be working quite well.


              Having picked up when the promotions are on each line, how do you want to highlight the cycle?


              btw I've created the promo flag using a Table Calculation, so it is easy to play with/adapt, but we can create this as an LoD, so can be coded as a real dimension (not requiring date, or product in the VizLoD)

              • 4. Re: Dynamic Highlights using Formula
                Stacy Hennig

                Hi Simon--


                I think this is a great start. The thing I would like to do is be able to click and select multiple dates in which a promo was run (for example week ending 12/12 - 1/2) where "us" was on deal and have the dates in the future highlight where we think those shoppers would come in again based on the average purchase cycle.


                So the rough math would look something like when you select week ending 12/12/2016 to 1/2/2017 highlight the following dates (similar to a reference band) where week ending 12/12/2015 + 16 weeks, 12/19/2015+16 weeks, 12/26/2015+16 weeks, and 1/2/2016+16 weeks.


                The business question is particularly around the "us" promotion that ran from week ending 2/20 -3/19 and if "them" ended up capitalizing on the purchase cycle to win over shoppers when they were low on this same sized offering from "us".


                Let me know if this makes sense or you need additional detail.


                Kind Regards,



                • 5. Re: Dynamic Highlights using Formula
                  Simon Runc

                  hi Stacy,


                  So yes that makes sense...in terms of what you want, how we achieve that is a different matter!


                  I'm having a think about how we might achieve this with actions, but not sure this is possible (Actions are either a filter, meaning it would filter data down to the selected dates, or highlight where we can't do any calcs to pick up what has been highlighted...in order to also highlight 16 weeks ahead)...I'll keep the gray matter ticking over as it's an interesting challenge!


                  A much simpler method is to use parameters...this will also make it much easier to perform an calculations as its easy to pick up the promo period and 16 weeks ahead. On the attached I've used 2 parameters to make the date selection, and then it shades that period, and 16 weeks ahead (or this can be changed to N weeks, via the "Sales Lag" parameter)


                  So for the 2 parameters...I created the following calculation


                  [Highlight Selected Period - Parameter]

                  IF ([End Date] >= [Promo Start Date] AND [End Date] <= [Promo End Date]) OR

                  ([End Date] >= DATEADD('week',[Sales Lag - Weeks],[Promo Start Date]) AND [End Date] <= DATEADD('week',[Sales Lag - Weeks],[Promo End Date]))

                  THEN 0



                  I then set up the reference lines on the Y-Axis (odd I know!, but we can't set these up on a discrete axis) and then shade above



                  As we also want to see the 2 products as different colours I've highlighted the Promotions (using our flag from before) with a Star (this is just done with a dual axis and the following calc)

                  [Spend on Promo]

                  IF [Promo Flag] THEN SUM([Spend]) END


                  Let me know if the parameter version might work...I thought I'd start with this one as it is the simplest way (and offers much easier calculations for each of the interested time periods). I'll keep thinking about the action driven version, but no promises on that one!!

                  • 6. Re: Dynamic Highlights using Formula
                    Stacy Hennig

                    Hi Simon--


                    This is EXACTLY what I was looking for.


                    Thank you so much for taking the time to work on this. It's going to open the doors for some very cool ways to plan promotions for the upcoming year.


                    Kind Regards,



                    • 7. Re: Dynamic Highlights using Formula
                      Simon Runc

                      Excellent news...and glad that did the trick...doing it with actions would be (if possible at all) very complicated, and would likely need some data re-shaping/blending. And as you can see you can also use similar logic to work up any calculations (eg. Sales in Promo Week on Promo Line vs (Sales in Lag week [16 weeks later] on Promo Line and Victim Line...and all that good stuff!), which would be super complicated (if possible at all) with actions.


                      ...Ah the holy grail of visual promotional planning!! an area i know well!...enjoy