Rolling 13 month data

[sunny bachan prasad]

Hi Gurus,
General Information:Relational Package,Database-Netezza

I have history data for 2 year.
How can i proceds to fetch the rolling 13 month data in raletional package.
For exmaple if the user run the report in March 2015 then he should be able to see the data from Feb-2014 to March-2015.
Thanks in advance

[TheFrenchGuy]

Hi,

you can put that in filter :

Code Select Expand

[Sales].[Time - month].[Month ID]
between
to_number (to_char({sysdate};'YYYY')-1)*100 + to_number(to_char(add_months({sysdate};-1);'MM'))
and
to_char({sysdate};'YYYYMM')

Just replace by your month id path.

This code recreate a month id (201503) :
_to_number (to_char({sysdate};'YYYY')-1)*100 => 2015-1*100 = 201400
_to_number(to_char(add_months({sysdate};-1);'MM') => 03-1 = 02
then to_number (to_char({sysdate};'YYYY')-1)*100 + to_number(to_char(add_months({sysdate};-1);'MM')) => 201400+02 = 201402
_to_char({sysdate};'YYYYMM') => 201503

[sunny bachan prasad]

Thanks french guy for your time.

I have prompt in report and what ever user will select in the prompt.report shoule show rolling 13 month data.
For example: if user will select jan 2015 then report should show data from jan 2015 to jan 2014.

[MFGF]

Thanks french guy for your time.

I have prompt in report and what ever user will select in the prompt.report shoule show rolling 13 month data.
For example: if user will select jan 2015 then report should show data from jan 2015 to jan 2014.

What kind of prompt? Date prompt? Value Prompt? What sort of item are you filtering on and what format is it?

MF.

[sunny bachan prasad]

Hi MFGF,
Sorry for incomplete information.
I have the value prompt and i am filtering on date item.it is in yy-mm-dd format.and i have two year of history data.

[MFGF]

Hi MFGF,
Sorry for incomplete information.
I have the value prompt and i am filtering on date item.it is in yy-mm-dd format.and i have two year of history data.

Hi,

Ok - so if it's a date you are filtering on and displaying in the value prompt, you can use the following approach in your filter:

[your date item] between _add_years(?your date parameter?,-1) and ?your date parameter?

Does this give you what you need?

MF.