building "rolling n" in RS, no FM access

[hespora]

Hi there,


in a relational model, among other data, let's say I have a "date" field (in timestamp format), and whatever measure, let's say sales.

What I want to build as a separate data item is a "rolling 5 days" aggregate of sales. So, for the date of 2016-03-17, i want this to actually have the sum of sales for days 2016-03-13 through 2016-03-17 (5 days). For the date of 2016-03-16, it should be the sum for 2016-03-12 through ...03-16, and so on.

Is there a way to build this in Report Studio alone?

//Edit: I realized, I need to be more specific. I cannot quite use moving-total, because this explicitly works on rows. Thing is, my date field does not contain all dates; only dates where sales are present. I need the rolling total by calendar days, however.


Kind regards,
Mark.

[Lynn]

If you filter your query for the desired date range then the sales figure will automatically reflect the rolling 5 day period. You would look at the current date and go four days back to get the five days.

Code Select Expand


[Your Date Field] between _add_days(current_date, -4) and current_date


If your report is filtering for more than just the 5 day period, then you can create a data item that only includes the sales if the date is between the desired dates.

Code Select Expand


case
  when [Your Date Field] between _add_days(current_date, -4) and current_date
  then [Your Sales Field]
  else 0
end