If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

How to roll up data for past 7 days?

Started by mmckeen1, 27 Oct 2010 12:51:23 PM

Previous topic - Next topic

mmckeen1

I'm trying to write a data item for a report so that I can show the aggregated results for the past 7 days, week over week.  My client wants the report run every Friday against data for the past 8 weeks.  Each column needs to show the aggregated results that represent the previous Friday to Thursday.  A report run on Oct 22 would have a column labeled 10/21, and the data would include results from the 15th to the 21st.  I'm working with relational data, and I've tried to write the data item as follows:

_add_days(_add_days([Time Period].[YearMonDay], 7),-(_day_of_week([Time Period].[YearMonDay],6)))

My problem is that the results include the data from the 'last Friday' -- in the example above, the data for Oct 22 is included, when I really only want data from the 15th to the 22nd.  I've tried different combinations, but just can't seem to get what my client is requesting.  Any help would be appreciated, thanks.

rockytopmark

Best solution, is include week # in your Time Period dimension.  All you would need to do then is include the Week in the report and group on it, as well as filter to return only the 8 desired weeks.

If this is not possible...

The next best option is to create aggregatable data items (measures) that return 1 if they meet some date based condition, otherwise return 0. For example...

Assuming Today is a Friday, the data item for the most current week might look like:

If([Time Period].[YearMonDay] between [derivation to get 7 days ago] and [derivation to get today])
Then (1)
Else (0)

So, taking that, you would need to create 7 more data items, each one representing a single week of the 8.

_add_days() is indeed the function that would make the most sense for the "between" derivations necessary for within the 8 data items.

HTH, M