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 ID||Purchase Date||View Date||# Views|
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?
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).