2 Replies Latest reply on Apr 26, 2012 9:05 AM by Martin Daniel

    Relative window_average based on a week parameter

    Martin Daniel

      Hi all !

       

      I am trying to build a view that uses window average based on a week parameter.

       

      My dataset is something like :

       

       

      date
      productCategoryproductNamesalesrevenue
      01/01/2012AA11020
      01/01/2012BB12020
      02/01/2012AA11020
      03/01/2012CC11530
      etc...



       

      I get the first calculation (sales vs average sales of 4 past weeks) using the following calculated field :

       

      SUM(Sales)- WINDOW_AVG(SUM(sales), -4, -1)/ WINDOW_AVG(SUM(sales), -4, -1)

       

      I can then display my products in rows and week numbers in columns displaying the evolution of all my products over the total time period (I cannot display the data I am working on so I made an example using the coffee dataset example, see workbook attached)


      What I am trying to do now is building a week number parameter that could display the evolution of all products for a given week (see screenshot below).

       

      I think the solution can be something like : http://www.tableausoftware.com/table-calculations but I cannot figure how...

       

      Many thanks for your help !

       

      mockup-sales evolution.png

        • 1. Re: Relative window_average based on a week parameter
          Jonathan Drummey

          Hi Martin,

           

          I noticed this post hadn't been responded to. Here's a workbook that hopefully meets your needs. I made a couple of changes, one is that I used the Category/Subcategory hierarchy instead of Subcategory/Product because the latter data was too sparse for the calculations to show much of anything. Secondly, I changed the color scheme to red/white/black diverging because red/green doesn't work for color-blind people, and your desired view had shown 3 colors.

           

          I did use a table calculation as a parameter-driven filter on week of Order Date.. Filters based on table calculations are applied after most all calculations are completed, so the filter can effectivel hide data from the view. Also, I had to put the filter calc on the Level of Detail in the view - in Tableau 7.0 table calc filters can only "see" dimensions that are on the Rows and Columns shelves, and since this view requires the Sub-category and WEEK([Order Date]) to be on the Level of Detail, also putting the table calc on the Level of Detail lets the instance of that calc on the Filter shelf "see" itself there and work.

           

          Let me know if this works for you,

           

          Jonathan

          • 2. Re: Relative window_average based on a week parameter
            Martin Daniel

            Wow thank you for this Jonathan, it works perfectly for me !

             

            Martin