Previous and Next two month data

[ambuj2k50]

I need to design a chart report where the revenue of the starting promotion start month and the previous two months and next two months should be shown.

what would be the code to find previous 2 months and next two months by using cognos functions ?

[blom0344]

_months_between   function

[ambuj2k50]

Thanks a lot Blom i ll give it a try :)

[ambuj2k50]

The _months_between function returns a integer value either in +ve/-ve by taking two timestamp parameters.

Exactly what i need is

Query1 contains

Promotion
Product
Planned revenue
Revenue
Month

Query2 Contains

Planned revenue
Revenue
Product
Month

Query 3 is Join between query 1 and 2

Product
Month
Planned Revenue
Revenue

I need to apply a filter in Query 3 so that only data for the 5 months (Promotion month+previous 2 months+ after 2 months) data will be shown.

Please sugest how to achieve that.
Thanks in advance

[ambuj2k50]

Attaching the XML.

[blom0344]

That would depend on the definition of month..  If you only use the 12 valued range:  1-12 / Jan-Dec then  calculations are a real pain.  If you take - for instance - the first / last day of the month (as date) into you model, then temporal calculations become much easier. The best approach is to have a dataitem that contains the month representation and another one to calculate with..

[ambuj2k50]

i tried with the filter with expression

cast([Query2].[Month],timestamp) between (_add_months((cast([Query1].[Month],timestamp),-2) and (_add_months((cast([Query1].[Month],timestamp),2)

But its giving the following error

An error occurred while performing operation 'sqlOpenResult' status='-28'.

[blom0344]

In your case what does [month] represent?  Integer (1-12) , String, Date?

[ambuj2k50]

String

[blom0344]

like what  :o?  If it represents a date format you can use it to cast to date/timestamp.

[ambuj2k50]

Resolved the issue by using Month(numeric) data item instead of Month data items.
Anyways...Thanks a lot for helping.

[blom0344]

If you mean the numeric values running from 1 -12 then you will run into issues transcending years. By the way, the idea of a peer-to-peer forum is to post the solution if a case is resolved. This is to ensure other developers can benefit from a topic (discussion)

[ambuj2k50]

RESOLVED

Attaching the xml of the report (with sample package).