2 Replies Latest reply on Feb 26, 2016 12:52 PM by pooja.gandhi

    Use historical data to create dynamic target

    Gregg Hirshberg

      I hope I explain, what we are trying to accomplish, well enough.

       

      Using the Superstore Sample data for example, we want to take the historical sales data from 2011, and use those figures as a goal to measure 2012 against.

       

      For example, in 2011, Furniture Sales totaled around $12.7M. So we'd like that to be our goal, and then if we filter the Product Sub-category, we'd like the target to change. Does anyone have a similar situation that could be used to help us accomplish this view?

       

      Thanks in advance!

        • 1. Re: Use historical data to create dynamic target
          Ben Page

          Hi Gregg,

           

          I think the LOOKUP() function is what you need. Depending on the date you have in the view, you can look back at previous dates:

          If you have Date broken out by year, this will bring back the previous year's Sales. You can add any dimensions to the view and it will update accordingly. Similarly, if you change Date to be broken out by month, the targets will update to reflect previous month's Sales. So, the assumption is that you have a hard and fast rule that the target is ALWAYS "Previous period's sales" (or some other fixed benchmark, say 1.2x previous period's sales). I've attached a workbook. Let me know if this helps.

          • 2. Re: Use historical data to create dynamic target
            pooja.gandhi

            Gregg!

             

            If you have access to Tableau 9.0 or higher you can LOD (Level of Detail) expressions to achieve this:

             

             

            The top axis (gantt bars) are the target values for 2011 and bottom axis (bars) are 2012 sale values. Filter order date on 2012 and click on the dropdown of the second pill on columns to turn it to a dual axis.