Rolling averages/sums for counts

[Former Member]

I am a new user (business objects 12 relaease 2)

I would like to build a report in web intelligence displaying the average count of the number of records per day, where the average is calculated for a rolling window of days.

For example a rolling daily average count of records using a 3 day window would be caluculated by:

Summing the counts of records for the last three days and dividing by 3

I would like to include this rolling average in a table for a specified of time period, say the last 30 days. My table would have two rows the top row would be the date and would hgave 30 entries, inbe for each of the previous 30 days, The scond row would have the 3 day rolling average for each of the 30 days. This table format will also allow me to graph the table

An additional problem arises becasue there are some dates that have no data. I need to include those dates, but Web Intelligence won't display them. Is there a way of displaying dates with no values in the table?

[Former Member]

John Berezowski,

In response to your question: "An additional problem arises becasue there are some dates that have no data. I need to include those dates, but Web Intelligence won't display them. Is there a way of displaying dates with no values in the table?"

The solution is to build a "backend table" in your data base that has all of the applicable dates accounted for. Then modify your universe by linking this new table to your pre-existing table and process as a left outer join (providing that this new table is on the "left" side, otherwise a full outer join will suffice due to the manner that you've built your table).

A technique you can apply to the other columns used in your original table would be the use of the "coalesce" function to avoid nulls and transform unjoined values to spaces (or zero or some other value for integers).

thanks,

John