2 Replies Latest reply on Oct 23, 2018 5:32 AM by nick liu

    Window Average with Null Values

    nick liu

      I have the following shipment data by [SHIPMENT #], [MONTH], [Origin to Destination].

       

       

      I am trying to list the CountD of [Shipment #] by Month, and by Origin-to-destination, and then I was trying to add a customized "Grand Total" to show the [Avg of last 2 months". 

       

       

       

      I used calculated field "Avg of Last 2 Months" as following:

       

       

      and then I used the Copied Month to do the table calculation:

       

       

       

       

      It seems to be working, However it did not work for the null values, e.g: it seems like ignore all the null values as it calculates the average of last 2 months:

       

      for Origin-to-Destination == B, the average of last 2 months Should be (2+0) / 2 = 1, not 2.   (I also attached the tableau file)

       

      Any one could help me on it please!  Thanks a lot !!!!!!