3 Replies Latest reply on Nov 29, 2018 3:57 PM by Yuri Fal

    52-Week Median Grid

    Kevin Cain

      Attempting to use WINDOW_MEDIAN to yield a grid (it's what the client wants) of territories (row) by week (columns) where each cell represents the median for a territory of the 52 weeks preceding that week (table across).  Each column has its own 52-week window.  I think I've got that covered with the WINDOW_MEDIAN table calc, but I also need to color/count cells where the calculated WINDOW_MEDIAN for that territory/week are below the Median for the particular week (table down).   Can this be accomplished / am I overlooking something?
      Ideally I'd be able to show the # of week's a territory fell below the week median for visible territories.  I attempted an LoD on the window function, but that is not permitted.


      I created a Viz using LoD and an adjustable block of 52-weeks where the client can use a slider to go back to prior weeks and get the overall results, but the client really wishes to get an overview of territories across the 52-week period at a glance.

      The linked Viz below is a recreation of the Viz using Superstore (States instead of Territories, Sales instead of % of Goal).  I feel tantalizingly close to a solution, but am stymied when I try to work out the final comparison piece.  Any help will be greatly appreciated.

      (Using Desktop 10.5.2 at work - I understand with Set Actions in 2018.3 I should be able to filter to only showing the 52-weeks, but for now I'm not filtering on date.)


        • 1. Re: 52-Week Median Grid
          Yuri Fal

          Hi Kevin,


          If you want to compare

          the Median (across States for each Week) of

          the Medians (across Weeks for each State)

          then a Nested Table Calculation could help.


          Please find the attached (based on Sample Superstore).





          BTW, there would be additional complications

          regarding whether Null Sales means Zero Sales,

          or how the view should be organised / filtered.

          • 2. Re: 52-Week Median Grid
            Kevin Cain

            Yuriy, this is extremely helpful.  Thanks for pointing the way.  I was having issues with the 52-week window crossing years (window_sum was resetting), but I realize that I need to start with a continuous Week(Order Date) then set it as discrete. 


            With a little effort I should be able to get what my client is looking for. 


            • Out of curiosity, is there a reason you added lookup(min(datetrunc('week',
            [Order Date])),0)
            to Detail?  I removed it and the results are the
            same.  I ask in case it’s a Jedi trick

            • 3. Re: 52-Week Median Grid
              Yuri Fal

              • Out of curiosity, is there a reason you added lookup(min(datetrunc('week',
              [Order Date])),0)
              to Detail?


              It was my attempt to filter out weeks of 2017 --

              to hide them actually when using this pill on Filters,

              but then the densified Marks (where the filter value is Null)

              would be hidden out of the view, making it less 'informative'

              (but less cluttered at the same time :-) You may try it as well.