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.
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.
[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.
case
when [Your Date Field] between _add_days(current_date, -4) and current_date
then [Your Sales Field]
else 0
end