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

# How to sum most recent 7 days in data?

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!

• ###### 1. Re: How to sum most recent 7 days in data?

This is as far as I was able to get, but hopefully it helps!  I turned [Date Transaction] into an attribute so I could compare dates to the max date without getting an error of aggregate/non-aggregate.  I also used the Total function for the Max date.  Here is a resource I found that might be useful: Re: Calculated Field that is max date.  I then created another variable to do a window_sum of the sales.

• ###### 2. Re: How to sum most recent 7 days in data?

Hi,

Thank you very much, indeed that gets me closer, and I made a tiny change to your "calculation 1" to make it show the sales number which now gives me the number I want repeatedly.

Then I used a table calculation filter to make sure I only saw the most recent one

LOOKUP(MIN([Date transaction]),0) = [Latest sale date]

and I think that gives me what I want.

Thank you very much for the help and inspiration!

The solution is the attached, sheet 4, if anyone would like it.,