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


      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?