Displaying current week record and previous week records

[sthabinash]

Hi All,
I am new to IBM Cognos and do not know much about it, so you may find this question ridiculous. sorry for that.

I have used the value prompt to select a certain week from the list of week records and used it to display the current week sales record. At the same time I am also trying to display the sales of the previous week.

I have sucessfully displayed the current week's sales records as required but am not able to display the previous week's sales record in the same table. Could anybody please help me!!!!

[wyconian]

Hi

There are (at least) 2 ways of doing this.  Possibly the best is to have a relative week field added to your calendar dimension.  This would calculate the number of weeks from the current week so last week would be -1, next week would be +1 and current week would be 0.

If you don't have access to either the fm model or database or you can't get someone  to do this the other way is to have a couple of sub queries one which holds data fro the current week and the other that holds data for the current week -1 week.  You canthen join these together into the main query and report against that.

Hope that helps, good luck :-)

[Lynn]

As another option in addition to wyconian's suggestions....

Use a single query that filters for both weeks. Create a data item for "current week" that has an expression something like below. Create another data item for "prior week" that has a similar expression but with different date criteria.

This approach allows you to read in the entire date range in a single query and then bucket the metrics by week. Obviously this approach is most sensible when there is a finite and relatively small number of buckets to deal with.

Code Select Expand


case
when [YourDate] between _add_days(current_date,-7) and current_date
then [YourSalesValue]
else 0
end


[sthabinash]

Thanx for the quick replies. That really helped a lot  ;D