Trending an aggregated metric weekly using set analysis?

[#1]

     Here's a tough one (for me anyway ) that I'm hoping someone in the community can help with...otherwise I'm going to have to do this through the load statement...

Let's say we have a record set that looks like this:

Date	Weekday	Value
11/30/2015	Monday	1
12/1/2015	Tuesday	1
12/2/2015	Wednesday	1
12/7/2015	Monday	1
12/8/2015	Tuesday	1
12/14/2015	Monday	1
12/15/2015	Tuesday	1
12/21/2015	Monday	1
12/22/2015	Tuesday	1
12/23/2015	Wednesday	1
12/24/2015	Thursday	1

The user will select a "day of the week" (let's say Wednesday, for example) and in my chart I want the date dimension to display each "Wednesday date" within the date range of the data, and the bars on the chart should total all of the values from that Wednesday date back through the prior Thursday. So each sum will be a Thursday to Wednesday sum, in this example.

Using the above data I would have the following Dates and Summed Values represented on the chart:

Date	Sum(Value)
12/2/2015	3
12/9/2015	2
12/16/2015	2
12/23/2015	3
12/30/2015	1

Is it possible to do this using set analysis? Or will I need to create a new metric in the LOAD which sums each of the last 7 days for each date in the data set in order to give me the flexibility to select any day of the week, display those dates as the dimension on the chart, but sum that date plus the previous 6 dates for each date represented on the chart?

Thanks for any and all help, even if the answer is a flat, "No, this cannot be done!"

[sunny_talwar · Accepted solution]

How did you get 4 for 12/23/2015?

I am getting this:

Dimension: =WeekStart(Date, 0, 2) =WeekEnd(Date, 0, 3)

Expression: =Sum(Value)

[#3]

Thanks for the reply, Sunny -  I'm sorry, that should have been a 3, and not a 4. I had initially wanted to trend on Thursdays, but forgot to correct that table when I changed the example to a Wednesday trend.

[sunny_talwar]

So is the solution provided above, something you can use?

[#5]

You, sir, are amazing. My hat goes off to you. I was making this waaaaay more complicated than it needed to be, and you brought me the simple (and best, and only) solution I've seen. Thanks for your quick reply!

[sunny_talwar]

No problem. I am glad it worked out well.