1 Reply Latest reply on Dec 5, 2016 11:42 AM by Stephen Rizzo

# Using date ranges from one source based on a single date from a second source

Hi Tableauers,

I have a bit of a complicated question, and after a lot of time spent trying a few things out, I reverted to browsing the community...only, my question is quite unusual so I couldn't really find anything quite right.

Essentially, I have two sources of data:

(1) Source A contains view dates and # views

(2) Source B contains purchase dates (and a bunch of other dimensions that aren't in Source A, but I want to view the data by)

The scenario I have is that I would like to take a 7 day average of views from Source A for the product ID that was purchased on the date in Source B. E.g.:

Product IDPurchase DateView Date
# Views
451401/10/201610
451402/10/201621
451403/10/201645
451404/10/201610
451405/10/20160
451406/10/20160
451407/10/20164
451408/10/201608/10/20165
451409/10/201675
451410/10/201610/10/201610
451411/10/201665
451412/10/2016

What I have tried already is to create a key [Product ID]+[Purchase Date] in both Source A and Source B. When I use that as the link, and then bring across # Views, it will bring across 5 for 08/10/2016, and 10 for 10/10/2016, but NULL for 12/10/2016 (which makes sense).

What I am trying to actually achieve is to bring across the AVG of the 7 day period from the purchase date, i.e. for product 4514 purchased 08/10/2016, I would like to identify that the prior 7 days would be 01/10 to 07/10, which would have a SUM of 90, and AVG of 12.9 views - I would like to see these values against the 08/10/2016 line, and similarly, for 10/10, I would like to see AVG 19.9, and 12/10 an AVG 22.7.

Does anyone have any ideas how this can be achieved?

Thanks!

• ###### 1. Re: Using date ranges from one source based on a single date from a second source

There are two ways that come to mind to calculate your 7-day look-back average. You could either do the calculation in a pre-processing step using custom SQL or as a table calculation in Tableau (something like WINDOW_AVG([# Views], -6, 0), assuming [# Views] is already an aggregation).