If then or Case for Month Prompt

[KristieLee1]

I have prompts for both month and year. My report shows both current month information and prior month information.

In order to show prior month information I have this...
?Month?-1, which I know is wrong.

What I need is something like this:
If ?Month?-1 = 0 then 12 (because what I need to return is December info and there is no 0 month) Else ?Month?-1

Also, if the prior month is 12, then the Year needs to be the prior year as well...
So if ?Month?-1 = 12 then ?Year? should be ?Year?-1 also.

I've tried Case statements and If statements.

I have two separate prompts, one for year and one for month.

Example User enters 1 in month prompt and 2009 in year prompt. My report will show data for jan 2009, and should show data for december 2008 as well.

Any help is appreciated. Thank you in advance.

 

[drlex]

It would be useful to know
1) what product you are using
2) how is your data being referenced/filtered - is it two data fields (year and month) or one date field?

In general, I would look to construct a date from the prompts, remove one month and then filter the data using the resulting date.

soi la, soi carré

 

[blom0344]

Kristie,

you are making this harder than needed. There are several Cognos functions that let you do time calculations with dates and months that only require the input of 1 date.
This enables you to have one nice date (as calender) prompt and then use the parameter associated in a filter with functions as:

_add_months
or:

_months_between

(basically the same reply as drlex I guess , sorry lex )

Ties Blom

 

[navshiv]

I had to do a similar exercise for my report. I can't use the date functions because our financial periods can spread over 2 different months. It's probably not the best way but it kinda works! The prompted period number is what I call [current month]. For [Previous Month] I do this:

IF (CAST_INTEGER([CURRENT PERIOD]) = 1)
THEN (CAST_INTEGER([CURRENT PERIOD]) + 11)
ELSE (CAST_INTEGER([CURRENT PERIOD]) -1)

Bit cowboy but does the trick for me.

 

[drlex]

navshiv,
That solution is fine if you only ever have a rolling 12 periods, otherwise how do you prevent matching periods from prior years from being included?

soi la, soi carré

 

[navshiv]

In my case I have different queries for current and previous year. Current Year is the prompt value. Previous Year = Current Year - 1. These are set in the filters.

Therefore periods from prior years won't be included.

Cheers

 

[drlex]

OK, so your case then parallels that of the thread starter, in that your consideration of a prior month also affects the year when the prior month crosses the year break.

Personally I prefer to construct a date field from a year & period data set, as a filter with a date range can be determined more easily than a slew of conditional statements. YMMV.

soi la, soi carré