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

# Problem with window functions when there are gaps

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:

 Date Value 1/01/2011* 1 8/01/2011 4 15/01/2011 2 29/01/2011 8

* (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:

 Date Value Intended Moving Sum 1/01/2011 1 8/01/2011 4 15/01/2011 2 7 22/01/2011 ? 6 29/01/2011 8 10

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:

 Date Value Actual Moving Sum 1/01/2011 1 8/01/2011 4 15/01/2011 2 7 29/01/2011 8 14

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

• ###### 1. Re: Problem with window functions when there are gaps

Tom,

The way I have addressed this gaps in dates problem in the past (although not with a moving sum) is to create an extra data source with all of the dates that you need, blending it to your data set, setting it up as a left join (with the optional side of the join on your data) and then using the date in your new data source as the date you use in the analysis

Does that help?

Cheers

Peter

• ###### 2. Re: Problem with window functions when there are gaps

Tom,

My suggestion above doesn't work as I have written it out.

I've tried to show you an alternative  in this workbook. My result doesn't match your 'Goal' but does give you data for every date and produces an actual moving sum. Does that get you nearer?

Peter

• ###### 3. Re: Problem with window functions when there are gaps

Thanks Peter, I'll give that a shot. A join is not ideal though as sometimes these data sets can be pretty huge, and the join will be costly. I guess I'll change this to a 'wouldn't it be nice if...' tableau supporting range based windowing. Cheers.

• ###### 4. Re: Problem with window functions when there are gaps

Thanks, that does work actually. The only difference is that you didn't use 'null if there are not enough values', once you do, it matches my goal exactly. Also, I hadn't thought about using another Tableau datasource to get Tableau to do the join on the date list itself, rather than pushing that join back into the DB. That at least gives me another option if doing the join in the DB proves a performance issue, because Tableau can wait to do it on only the results from the DB that actually NEED to be joined, whereas the DB will join on the entire source resultset in some cases, it could prove faster.