2 Replies Latest reply on Mar 10, 2016 7:55 PM by Venkata Dantuluri

    Table calculation and LOD

    Venkata Dantuluri

      HI all,

      I am using the superstore datasheet with the '2014' replaced by '2016' .

      The requirement is..

      Average of the  sum of sales of the previous 3 weeks. This is to be achieved for the last 3 weeks.

      If we are in week 0 then...

       

      Week(going backwards) --   sum(sales)

      1                                        --   10

      2                                       --     15

      3                                        --      20

      4                                      --       5

      5                                      --      15

      6                                      --      10

       

      What we need to achieve is  

      Week      --   Window_avg of sales

      1       --     13.33 = (15+20+5)/3

      2      --      13.33 =(20+5+15)/3

      3      --     10 = (5+15+10)

      We can achieve this for all the previous weeks. However, to show only the last weeks, I used the C.F...

      [ last 3 weeks]=[Order Date]>(DATETRUNC('week',today)-22)  in the filter and selected TRUE.... but that impacts the result set, expectedly.

      Some of you might suggest me to hide the weeks beyond the 3rd week, but I am looking for an alternate solution.

      Foll are the C.Fs that I have used.....

      Window_avg of sales=window_avg(sum(sales),-3,-1) and

      last 3 weeks=[Order Date]>(DATETRUNC('week',today)-22) which is placed in the filter.

      Please help!!

      PLease check the attached twbx file for a better picture.

       

       

      Thanks,

      Raju

        • 1. Re: Table calculation and LOD
          Dan Sanchez

          Hello Venkata!

           

          I think all we need to do is just modify the formula used for the [last 3 weeks] filter to use this:

           

          LOOKUP(MIN([Order Date]), 0)>(DATETRUNC('week',TODAY())-22)

           

          By filtering with a table calc (the LOOKUP function) we are able to "filter" out the rows but still allow the WINDOW_AVG calc to reference them.  Table calc filters act more like hiding the rows instead of completely filtering them from the view.

           

          How does this look?

           

          3-10-2016 8-17-28 AM.png

           

          Thanks!

          1 of 1 people found this helpful
          • 2. Re: Table calculation and LOD
            Venkata Dantuluri

            HI Dan,

             

            Thank you very much.

            Just 1 more thing... can we also have the no: of reds and greens for each row.

             

            Thanks again.