3 Replies Latest reply on Sep 26, 2014 9:57 AM by caitlin.donaldson

    How to sum most recent 7 days in data?

    adam.medcalf

      Hi,

       

      This sounds like an easy one, but have been struggling a while on it!

       

      What I have is a table of data, 1 row per sales transaction, together with the date of the transaction and the customer ID. I do not care about the customer ID but I would like to show on a dashboard the some of the 7 most recent days available, where the last is the most recent date in the data (which is not necessarily going to be TODAY()).

       

      To help explain, if I have this:

       

      DateSales
      11/01/20141
      12/01/201411
      14/01/20141
      16/01/20141
      19/01/20141
      20/01/20141
      21/1/20141

       

      I would like to display the number 4, because:

      - the most recent date in the dataset is 21/01/2014

      - that means the start of that week, 6 days before, is 15/01/2014

      - And there are 4 sales between 15/01/2014 and 21/01/2014

       

      I have found on the forum some examples similar 7 days calculations but they generally seem to involve the past 7 days starting today, whereas mine needs to be the past 7 days starting at the latest date in the data.

       

      I have had success in Tableau with table calculations of having it highlight the individual records concerned (see attached in orange) but bizarrely I can't make it sum just them up. I also tried a rolling sum but that didn't work as it always takes the last 7 records even if some are >7 days before.

       

      I hope that makes sense and very grateful for any help. Attached a workbook with sample data already in. In reality the data updates every so often so it would need to dynamically recalulate the latest date.

       

      Thanks!

       

      Adam