3 Replies Latest reply on Sep 1, 2016 7:11 AM by chris.moore.11

    Moving average using LOD

    Victoria Snell

      I'm stumped trying to calculate a moving average using a LOD -- nested LODs, actually. The need is to be able to see the previous month's value (so only a one month moving average) as a column in the data extract independent of the visualization. The data is structured with a row for every date for every item (ptno_id).

       

      My understanding is that I can't use table calculations because they won't be relevant at the raw data level in the export. Also, I generally like to avoid table calcs and want to prove this can be done with LOD... Anyway, correct me if I'm wrong here.

       

      This is the calculation I'm working with, but it returns null for all dates.

       

      {FIXED [Ptno Id]:

      SUM(

      IF [Rcd Proc Dt 2]=

              {FIXED [Ptno Id], [Rcd Proc Dt 2]:

              MAX(DATEADD('month',-1,[Rcd Proc Dt 2])) }

      THEN [Weighted Actual (Units)]

      END)}

       

      I fear that the IF condition is only being evaluated for a single record, and the date will never equal last month's date for the same record. Does the order of operations not check for all dates before the sum occurs? Is there something else I'm missing here?

       

      Help is greatly appreciated!

       

      -Victoria

        • 1. Re: Moving average using LOD
          chris.moore.11

          You are right about the if statement. It will be null every time because the date cannot be the date minus a month. Although you don't want to use them it seems like table calcs are the way to go. I can't think of a way to do it with just the one data source and lod calcs. Another strange way would be duplicate the source and do a blend on the month and the previous month, it would be something along the lines of whats attached...

          • 2. Re: Moving average using LOD
            Victoria Snell

            Thanks for confirming. The problem with table calcs is that the chart I want users to extract data from doesn't include all dates, so I can't use the date as the "moving along" field. I may just have to do this calculation outside of Tableau.

             

            I am still confused about the order of operations within LOD. Why is it that the following will work with a constant date instead of the nested LOD? Is the inner LOD not computed first?

             

            {FIXED [Product Group], [Model Scope], [Ptno Id]:

              SUM(

              IF [Rcd Proc Dt 2]=#07-01-2016#

              THEN [Weighted Actual (Units)]

              END)}

            1 of 1 people found this helpful
            • 3. Re: Moving average using LOD
              chris.moore.11

              The first LOD will get computed, just that "if" statement will always be false.

               

              This is the calc from your original post:

              {FIXED [Ptno Id]:

              SUM(

              IF [Rcd Proc Dt 2]=

                      {FIXED [Ptno Id], [Rcd Proc Dt 2]:

                      MAX(DATEADD('month',-1,[Rcd Proc Dt 2])) }

              THEN [Weighted Actual (Units)]

              END)}

               

              This is the first lod.

              {FIXED [Ptno Id], [Rcd Proc Dt 2]:

                      MAX(DATEADD('month',-1,[Rcd Proc Dt 2])) }

               

              That will always be one month before [Rcd Proc Dt 2] for that id. So the dates will never equal one another, and that "if" will never be true.

               

              Hopefully this explanation helps. Sorry, i cant help more.

               

              Heres a photo of what that first lod will bring back.

              nested lod.PNG

              1 of 1 people found this helpful