4 Replies Latest reply on Feb 28, 2012 9:35 PM by Tom Walter

    Problem with window functions when there are gaps

    Tom Walter

      Hi,

       

      I have a data source which frequently has gaps in the data where we don't see anything for a particular date.

       

      I am trying to do a 'moving sum' style calculation, however I want to ensure that the calculation is done over a certain timeframe regardless of the existence of gaps in the data. Eg if the raw data looks like this:

       

       

      DateValue
      1/01/2011*1
      8/01/20114
      15/01/20112
      29/01/20118

       

      * (dd/mm/yyyy, UK date format)

       

      Then I'd like the moving calculation to account for the fact that the week beginning 22/01/2011 had no data, and therefore calculate the moving sum for the 29/01/2011 based off the data for 15/01 + 29/01 only. Eg:

       

      DateValueIntended Moving Sum
      1/01/20111
      8/01/20114
      15/01/201127
      22/01/2011?6
      29/01/2011810

       

      However, it seems that Tableau window functions only work on the basis of rows preceding, and can't be made to account for gaps. So using the built in moving sum table calculation, I actually get:

       

      DateValueActual Moving Sum
      1/01/20111
      8/01/20114
      15/01/201127
      29/01/2011814

       

      I've tried messing around with various way's of representing the date field (continuous, discrete, show missing values etc), however none of them are giving me the result I want.

       

      Is there some other way to achieve what I am after?

       

      I have attached a packaged workbook with the example I've given, the 'Goal' sheet shows what I would like to get, the 'Window' sheet shows what the built in windowing functions are giving me.

       

      Update: I should add that the actual number of weeks to roll needs to be a parameter, so solutions that require using multiple lookup() calls will not work. I've reattached the workbook with the parameter in place.

       

      Cheers

      Tom

       

      Message was edited by: Tom Walter