Question from tgs728 on Dec 6 at 12:40 AM
Hi Gurus,
I had a requirement as follows.
When current month is between January and March then I need the data from previous year and if current month is in between April and December I need to get the date for current year.
I tried the following but no result
([month] <= 3 and [year] > '2006')
or
( month] > 3 and [year]='2007')
--- second method
case statement but no use.
Can you please suggest on this.
Thanks,
TGS
Hi,
Remove the single quotes for Year.
it should be [year] = 2006
try this.
Why do it so specifically?
If I understand you correctly, what you need is to run the data for last year if the current month (That is, the month in the real world when the user runs the report) is before April, and for current year (The year in the real world) if the report is ran past April. This should work as a filter expression:
(extract(month,current_date)<=3 and [YEAR_NUM]=extract(year,current_date)-1)
OR
(extract(month,current_date)>=4 and [YEAR_NUM]=extract(year,current_date))
explanation:
extract(month,current_date) gets the month at the time of report execution.
when it's 3 or under, [year] field is filtered to equal last year (we extract current year and subtract 1).
when it's 4 or over, [year] field is filtered to equal this year.
Hi Nimrod,
Appreciate your help,I tried the logic you have given its not working its giving every time current year details.
Thanks,
TGS
Of course it does, because now it's December... :)
If you want the user to select a month and year, and based on that get either the year the user selected or the year before that, use something like that:
(?month_parameter?<=3 and [year]=?year_parameter?-1)
OR
(?month_parameter?>=4 and [year]=?year_parameter?)
Instead of ?month_parameter? and ?year_parameter? insert the parameters you're using for the user to select month and year.
Hi Nimrod,
I tried changing the current_date to 01/feb/2013 then i tried the logic but still no luck, in our case there is no need of parametres
Thanks,
TGS
current_date is based on system date. Where did you change your date?
Hi,
Is Month User Selection....???
hehehe he might be trying to ask how to test the logic is working correctly....
... either you wait till next year or hard code the values
Quote from: Nimrod Avissar on 09 Dec 2013 01:49:42 PM
current_date is based on system date. Where did you change your date?
hi guys,
i have been using the logic i am not getting the proper data according to the logic.
[month]< 4 and year[DATE] = year(current_date)
or
[month]> 4 and year([DATE]) = year(current_date) -1
there are no errors in the expression, everytime the data is coming for current_year.
My database is db2.
please any help
Quote from: tgs728 on 06 Dec 2013 05:54:55 AM
Question from tgs728 on Dec 6 at 12:40 AM
Hi Gurus,
I had a requirement as follows.
When current month is between January and March then I need the data from previous year and if current month is in between April and December I need to get the date for current year.
I tried the following but no result
([month] <= 3 and [year] > '2006')
or
( month] > 3 and [year]='2007')
--- second method
case statement but no use.
Can you please suggest on this.
Thanks,
TGS
Can you try with this for current year data....
[YourDateColumn] >=
_add_months (_first_of_year(current_date),9)