If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

filtering the data by date

Started by tgs728, 06 Dec 2013 05:54:55 AM

Previous topic - Next topic

tgs728


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

Satheesh

Hi,
      Remove the single quotes for Year.


it should be  [year] = 2006




try this.








navissar

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.

tgs728

Hi Nimrod,

Appreciate your help,I tried the logic you have given its not working its giving every time current year details.

Thanks,
TGS

navissar

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.


tgs728

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

navissar

current_date is based on system date. Where did you change your date?

Satheesh

Hi,


Is Month User Selection....???








Francis aka khayman

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?

tgs728

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

simi123

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)