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 !!!!!!