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

    How to sum most recent 7 days in data?




      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:




      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.