4 Replies Latest reply on Dec 15, 2015 9:47 AM by Rody Zakovich

    Problem in including missing data in Window Calc, average

    Jone Kutty

      I have set of data running for one year. Data is grouped in buckets - weeks

       

      However, there are days / weeks where there no data. So in window calc, Tableau does not consider missing data

       

      when calculating moving average for 4 periods, Tableau is ignoring records where there is no data. Even if forced to include missing data, calc only includes records with data.

       

         

      DateDataRecords to be includedRecords are getting included
      12/7/20151001 1
      11/30/20152502 2
      11/23/20153
      11/16/20153004 3
      11/9/2015
      11/2/2015
      10/26/2015100 4

       

      Can anyone share their thoughts or views to fix this?

       

      Thanks,

        • 1. Re: Problem in including missing data in Window Calc, average
          Rody Zakovich

          Hello Jone,

           

          Is this similar to the problem you are facing?

           

          Inserting Breaks for Sparse Data in a Running Total

           

          If not, let me know.

           

          Regards,

          Rody

          • 2. Re: Problem in including missing data in Window Calc, average
            Rody Zakovich

            Also, if the Goal is do an average (where we set the value of missing data to Zero) then adding ZN() to your Measure might resole the issue.

             

            WINDOW_AVG(ZN(SUM([Measure])), -3,0)

             

            This way the NULL Values gets populated with Zero (Once you turn on "Show Missing Values").

             

            Regards,

            Rody

            1 of 1 people found this helpful
            • 3. Re: Problem in including missing data in Window Calc, average
              Jone Kutty

              Rody,

               

              Thanks for the reply, so far no luck.

               

              Here is what Tableau is doing - when I ask Tableau to take Window Average of Sum of the last 4 periods - using Moving Average function the one with -3, including current record, Tableau ignores Week buckets where there are no data. This happens even if I put zn or ifnull etc.

               

              If you look at the table I gave in original post, that is the behavior demonstrated by Tableau - that is after using Zn as well as showing missing values. I even tried to bring in all dates using left join, but moment I ask Tableau to do Moving Calc with Window Avg, it just ignores all week buckets where there is no data, and only picks up columns or date bucket where there are data.

               

              Any thoughts....

              • 4. Re: Problem in including missing data in Window Calc, average
                Rody Zakovich

                Ok, let me take a step back.

                 

                So your source data looks something like this

                 

                 

                Where you have missing Dates between each week. Before we do anything special, it looks like this in Tableau.

                 

                 

                When you "Show Missing Values" the table looks like this

                 

                 

                And you want to do a WINDOW_AVG(SUM([Data], -3, 0), with the Missing Dates being padded out, where missing weeks is getting a value of Zero?

                 

                 

                Or do you want it to simply do the Avg of the last 4 periods and Null Values remain NULL?

                 

                 

                I added "Actual Data Points to Avg" so that you can see what the WINDOW_SUM in Each Partition is getting Divided by.

                 

                When Null values are left NULL

                 

                When NULL Values are counted as Zero

                 

                 

                I'm trying to figure out where you are at, and what problems you are facing, that is not allowing you to do this.

                 

                Regards,

                Rody