3 Replies Latest reply on Jan 30, 2017 8:44 PM by Shinichiro Murakami

    Average 3 years by week on an existing calc

    stephen.ta.0

      Hi I have an existing calculation that calculates the week to week difference in change by % in my current workbook.

       

      I have data that spans 5 years and now want to take the percent difference average for example of week 1.  How would I be able to do that without using table calculations?

       

      thanks

        • 1. Re: Average 3 years by week on an existing calc
          Patrick A Van Der Hyde

          Hello Stephen, 

           

          Do you have a workbook to show an example of what you are doing? 

           

          As I understand it, you want to compare any specific week across the five years of data to the first week of data without using a Table Calc?   That sort of isolation of the data from the first week of data is probably easiest to do if the first week of data can be made its own data source for comparison purposes but seeing a bit more information about the data would be needed.

           

          Patrick 

          • 2. Re: Average 3 years by week on an existing calc
            stephen.ta.0

            Hi Patrick sorry for the confusion as I am be confusing when explaining things.

             

            So I want to look at the difference by percent change from week 2 compared to week 1 in ending inventory.

             

            (ZN(SUM([# of apples])) - LOOKUP(ZN(SUM([# of apples])), -1)) / ABS(LOOKUP(ZN(SUM([# of apples])), -1))

             

            I used that to calculate the percent difference from the previous week,

             

            I then went to do a calculation that would look calculate the average percent change in inventory from example :2012,2013, and 2014 for week 2. 

             

            Example 2012 week 2: 3% change from week 1

            2013 week 2: 5%

            2014 week 2: 4%

             

            average from 2012-2014 for week 2 would be 4%.

             

            How would I have do that in a calculated field so I can apply it to a forecast in tableau?  I am inserting all of this into a line graph just for context of what I am doing.

             

            thanks

            (ZN(SUM([Ending Unprocessed Receipts])) - LOOKUP(ZN(SUM([Ending Unprocessed Receipts])), -1)) / ABS(LOOKUP(ZN(SUM([Ending Unprocessed Receipts])), -1))

             

            A

            • 3. Re: Average 3 years by week on an existing calc
              Shinichiro Murakami

              Hi Stephen

               

              I'm not sure I understand your request correctly because the number is

              quite different from your example in the post.

              Anyways,

               

              // Same as your calc

              [Delta % from last year]

              (ZN(SUM([# of Apples])) - LOOKUP(ZN(SUM([# of Apples])), -1)) / ABS(LOOKUP(ZN(SUM([# of Apples])), -1))

               

              [Avg Delta from last week 5 years]

              window_avg([Delta % from last year])

               

              I

               

              Talking about forecast,

              I don't think you can use these calculated numbers as Tableau's "Forecast" functions dynamically.

              But you can create another worksheet with using these numbers as independent Viz.

               

               

              Thanks,

              Shin