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

    mohsin.khan.1

      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!