7 Replies Latest reply on Sep 20, 2016 7:54 PM by michael shin

# Capturing last 3 WEEKS of data

Hello all. I have a data-set that will always be in the past.  Relative date function anchors either to today's date or a specified date.  I currently have just DATES in my data-set. How do I capture rolling last 3 WEEKS of data?  I am thinking of using a set, but having trouble with capturing last 3 weeks given my data set has DATES.

• ###### 1. Re: Capturing last 3 WEEKS of data

Hi Michael! Have you tried putting WEEK(Date) into one of the shelves and then using the Moving Calculation Quick Table Calc?

EDIT: I just re-read your post and see that you want a rolling value. You could do something more like this instead:

IF FIRST() % 3 = 0 THEN

SUM([Value])

ELSE

SUM([Value]) + PREVIOUS_VALUE(0)

END

This uses the modulo operator (%) to get the remainder of dividing FIRST() by 3, so it basically resets every 3 indices.

• ###### 2. Re: Capturing last 3 WEEKS of data

Thank you Dave for the quick response!  So, how do I capture the last rolling 3 weeks w/o the metric even being in the display?  And, let's assume that the maximum date is December 2015...like in the sample Tableau data set?

• ###### 3. Re: Capturing last 3 WEEKS of data

You're welcome! And as far as I know, you can't really do that in Tableau. Your best bet might be to do the rolling calculation upstream of Tableau.

For instance, you could use custom SQL in the data source editor or in the database itself. See: Rolling Averages in MySQL and SQL Server

• ###### 4. Re: Capturing last 3 WEEKS of data

Hi Michael,

I'm assuming you want to show 3 weeks prior to your maximum date.  If so it's pretty easy with LOD.  Here is the code you would use for superstore based on order date, [Order Date] > DATEADD('week',-3, {FIXED : MAX([Order Date]) }).  The code is for a Boolean field which you would drag to filters and set to True.

You can modify it to work with your date field.  Let me know if you have any questions.

Regards,
Ivan

1 of 1 people found this helpful
• ###### 5. Re: Capturing last 3 WEEKS of data

Ah, is that what you meant? Just the last 3 weeks' data? If so, Ivan's got it. I thought you meant a true rolling 3-week-over-week calculation.

• ###### 6. Re: Capturing last 3 WEEKS of data

I'm not entirely clear that is what is desired but that was my interpretation.

• ###### 7. Re: Capturing last 3 WEEKS of data

Thanks Ivan.  That is what in fact I wanted to accomplish.  Seems sets are not ideal. LOD is the way to go.