3 Replies Latest reply on Jan 4, 2017 1:42 PM by Christina Gremore

    Calculate last X days rolling

    Robert Hixson

      Attached are the data source and packaged reports I will be referring to in this post. The Tableau report uses version 10.1.0.

       

      The overall goal, is to make it so that I don't need 3 sheets for this report, I can use a parameter to set the index days.

      In the Excel document there is a column called "Planting Index", this column is a total of the last X (7, 10, or 14) days of the different between the Min and Max temperatures for the given days. The reports are setup to show when the Planting Index first overlaps the GDD line that is set by a parameter, and then displays the planting date X (7,10,14) days before that date.

       

      What I would like to do is remove that calculation from Excel, and have it setup as a parameter in Tableau and Tableau calculate that value internally like a rolling total that only takes into account the last X days.

       

      Any thoughts?

        • 1. Re: Calculate last X days rolling
          Christina Gremore

          Hi Robert,

           

          You can achieve your Planting Index calculation in Tableau with this formula:

           

          WINDOW_SUM(SUM([GDD]), -6, 0)

           

          If you create a parameter with the values 7, 10, and 14 for the end user to manipulate, you'd want the calc to look like this:

           

          WINDOW_SUM(SUM([GDD]), (([Days Parameter]*-1)+1), 0)

           

          (This is because you're counting the '0' day, the date itself, as the 7th day, so technically you only need to look back 6 rows farther.)

           

           

          If you plugged this Days Parameter into your Proposed Planting Date calc, you would only need one Excel sheet and one Tableau sheet, and then your end user can check out all three options in a single view.

           

          If this helps resolve your issue, please mark my answer as the correct one. Thanks!

           

          -Christina

          • 2. Re: Calculate last X days rolling
            Robert Hixson

            That works, but these leaves me with another issue. The reference line I have setup from the Date at the bottom axis, I would need this to move as well. It is currently setup to find the date when the Planting Index >= GDD Threshold and then moves back X number of days. When I build the new calculated field, when I attempt to use that field in the calculation, my line disappears. Thoughts?

            • 3. Re: Calculate last X days rolling
              Christina Gremore

              Hi Robert,

               

              Because the new calc is a table calculation, it converts your "Proposed Planting Date" field to a table calculation as well, so you'll need to specify how it should calculate it. Right-click on the pill on the detail shelf and select "Compute Using > F1." This will bring it back.