3 Replies Latest reply on Dec 24, 2012 9:22 AM by Shawn Wallwork

    Horizontal YTD Trend Line

    Marc Tollin
    Marc Tollin Dec 3, 2012 3:07 PM

      I have daily time series data that I am aggregating by week, and plotting for trailing 5 weeks (so think a line chart connecting 5 points)

       

      One of the fields, for example is AGG(CTR)=sum(clicks)/sum(Impressions)

       

      Another one of the fields, for example, if MediaCost=sum(MediaCost)

       

      How can I plot a horizontal line through the CTR graph showing the aggregate YTD value? YTD CTR=ALL CLICKS (YTD)/ ALL IMPRESSIONS (YTD)

       

      Similarly, for a field like MediaCost, how can I show a YTD weekly horizontal average? This line would have a value equal to (YTD MEDIA COST)/(# OF WEEKS SO FAR IN YEAR)

       

      The people who are viewing this like to see how what the data compares to the YTD value (which continuously updates as you add data), and I'm having a lot of trouble doing this in tableau without hard coding it. Does anybody have any ideas?

       

       

      I can't post the workbook/screenshot because the data is confidential.

      • 681 Views
      • Tags:
        • 1. Re: Horizontal YTD Trend Line
          Andy Cotgreave
          Andy Cotgreave Employee Dec 4, 2012 1:31 AM (in response to Marc Tollin)

          Without knowing exactly how your data is set up, you will be able to solve this with a combination of REFERENCE LINEs and TABLE CALCULATIONS. Reference lines allow you to draw horizontal trend lines. Table calcs allow you to work with any value of any mark or marks on your view. There are quick start guides on both of these areas: http://www.tableausoftware.com/support/manuals/quickstart

           

          I've attached an example with a reference line showing most recent sales value - a bit simpler than your requirements, but i hope you get the idea.

           

          Andy

          1 of 1 people found this helpful
          • 2. Re: Horizontal YTD Trend Line
            Marc Tollin
            Marc Tollin Dec 24, 2012 7:11 AM (in response to Andy Cotgreave)

            Thanks Andy. This is not quite what I'm looking for. In the case of most recent sales, the view would be a line chart of "most recent sales" (aggregated weekly) but the reference line would be the YTD weekly sales volume.

             

            Basically the question this would answer for someone looking at the view (and maybe this will inspire other graphical solutions): "Okay, I see that this chart for the past 5 weeks shows sales declining a bit. But the values themselves don't mean much to me because the program has been fluctuating so much this year. I wish this graph had a reference line in it that would tell me, in the year 2013, what we could expect (on average) of sales during an "average" 2013 week. This would give me a way to say 'OH okay, sales are fluctuating in line with YTD values, so slightly above and below that means things are as normal. If the sales line was wiggling far below this reference line than I know I have to call the sales team and yell at them."

             

            All this requires is a table calculation of YTD values that are not in your view. Does tableau make this possible in a single view?

            • 3. Re: Horizontal YTD Trend Line
              Shawn Wallwork
              Shawn Wallwork Dec 24, 2012 9:22 AM (in response to Marc Tollin)

              Marc,

              1. Duplicate your data source
              2. Remove all linking fields
              3. In the copy of the data source create your CTR calc: (SUM([Clicks]))/(SUM([Impressions]))
              4. Put this on level of detail, and add the reference line as MAX or AVG or MIN (but not SUM).

               

              See Attached.

               

              --Shawn

               

              PS: The data in this workbook is sensitive, but since I took 5 minutes to remove the names, and scramble the media costs, I am able to share it with you. In the future you can do the same.