Adding or subtracting a year (or months) from a prompt

[17813a]

Apologies if this has been discussed before (or is ridiculously easy). I'm working within Cognos in sql.

I'm trying to find a way to find data for the same time last year as the month selected by the user in a prompt. I'd like to retrieve the same data for twelve months prior to a YoY growth variable.

Current code:
with
monthsago as
(select trunc(add_months(sysdate, -#prompt('p_month')#),'mon') as y , last_day(trunc(add_months(sysdate, -#prompt('p_month')#),'mon'))  as z from dual)

prompt format is 'YYYY-MM'

[Francis aka khayman]

you are using SQL you say?

i'll let the others discuss the virtue (or otherwise) of using SQL when generating cognos report and of posting SQL questions in a cognos discussion thread heheheeh just kidding... sort of ...

anyway, to answer the question, it would depend on the database back end you are using. if you are using oracle for example, then ADD_MONTHS comes in handy.

oh, google is a useful tool.

[bvk.cognoise]

Hi ,

First extract "Month" from selected Year or if your directly using month for selection then use following expression

[Month] = _add_months ([Month Extracted], -11)

then aggregate measure accordingly.

Regards
bvk

[BigChris]

Ok, let me see if I've got the requirement right. You want the user to enter a month number, then you want to get the data for that month one year ago...assuming that's right, I think you want something along these lines (you might need to tweak it depending on your environment):

[Date Field] between _make_timestamp(year(getdate())-1,?MonthPrompt?,1) and _last_of_month(_make_timestamp(year(getdate())-1,?MonthPrompt?,1))

If you want to take into account the year as well (I've just noticed the format of your prompt) you'll probably need to break the prompt into two, one for year and one for month, then replace the year(getdate()) part for ?YearPrompt?

C