Cognos 11 aggregate sales between 2 dates

[zgeorge]

Cognos masters,

I have a prompt page that asks for a start and end date. What I need to do is aggregate sales between those two dates. The trick is that I need to find the aggregate value in a data item instead of a filter the whole query by those two dates. The reason being, I also need to aggregate the same sales but for the prior period. So I can't just filter the query but instead need to create the data item that will give me the aggregated value between the two dates.

I hope I was clear in my requirements, please ask questions if need be!

Best,
Zach George

[adam_mc]

Create a calculation for the new Data Item as follows:

if ([Sales Date] between ?Start Date? and ?End Date?)
then ([Sales])
else (0)

Then, set the Aggregation to Total.

This in effect, totals all the Sales you wish plus zeros for all the other dates.

Hope this helps,
Adam.