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
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.