Rolling Period

[kevbrown]

I'm looking to create a bar chart of a count over the last 7 days by day, so for today, sunday to saturday, tomorrow will show Monday to Sunday.  I'm sure this is easy but I can't get to grips with it.  I have a date and week fields in my data.

[swuehl · Accepted solution]

Try something like this:

Create a bar chart with dimension DateField and as expression something like this (assuming a date format like 'MM/DD/YYYY' for your DateField, adapt accordingly

=Sum({<DateField = {">=$(=Date(Today()-6,'MM/DD/YYYY'))<=$(=Date(Today(),'MM/DD/YYYY'))"}, WeekField=>} Value)

[kevbrown]

Thanks, thats great. Next question, I now want to add another aspect to the calculation. So as well as the following expressions - =Count(DISTINCT{<[Date] = {">=$(=Date(Today()-6,'DD/MM/YYYY'))<=$(=Date(Today(),'DD/MM/YYYY'))"}, [Financial Week]=>} [Issue ID]) - I want to look at only where the field  Reporting Area = Dissatisfaction

[swuehl]

That should be pretty simple, too:

=Count(DISTINCT{<[Reporting Area]={'Dissatisfaction'}, Date] = {">=$(=Date(Today()-6,'DD/MM/YYYY'))<=$(=Date(Today(),'DD/MM/YYYY'))"}, [Financial Week]=>} [Issue ID])

A Primer on Set Analysis

[kevbrown]

Superb, worked a treat. Next question. Say i want a rolling 13 week. I do have week in my data so would i just swap week for date or even look use the current criteria but use 13 weeks worth of days, so, -91

[swuehl]

You mean, replace the -6 by -91?