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
Hi,
you can put that in filter :
[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
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.
Quote from: sunny bachan prasad on 24 Mar 2015 12:35:28 PM
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.
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.
Quote from: sunny bachan prasad on 25 Mar 2015 10:49:16 AM
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.