Solved

Yearly Date expression in cognos

Posted on 2013-01-18
5
544 Views
Last Modified: 2013-01-23
Hi,

Can anyone please help me on how to write an automatic expression for yearly date filter.

Year  tarts from OCTOBER . For example  OCTOBER 2012 - OCTOBER 2013 is an year
0
Comment
Question by:new_perl_user
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 8

Expert Comment

by:SNeupane
ID: 38794821
Is it a hard coded filter or dynamic?
If hard coded, you can simply create a data item called 'Custom Year' with expression like below and use it for date filter:
Date between 10/1/2012 and 10/1/2013

For dynamic/rolling, you have to create calculated data items depending upon how many years you want users to see for data selection.
0
 

Author Comment

by:new_perl_user
ID: 38795438
I am looking for calculated data item for one year.. can you please let me know how to create a calculated expression for this...
0
 
LVL 8

Expert Comment

by:SNeupane
ID: 38797231
if ([Date Field] is between 2012-10-01 and 2013-10-31) then ('Current Fiscal Year') else null

That should work. Give it a try.
You may have to cast your date field if it's not in yyyy-mm-dd format.
If it doesn't work, I'll test at work on Monday and follow up.
0
 

Author Comment

by:new_perl_user
ID: 38805755
The above one is like hard-coding the dates right, I need help for automatic one like below where I am using for Quarterly data.

_first_of_month(_add_months(current_date,-3))
0
 
LVL 8

Accepted Solution

by:
SNeupane earned 500 total points
ID: 38807335
Okay here is the deal.
Create a query calculation for current fiscal year as below:
if([Date Field] between (cast(cast(extract(year from current_date)-1,varchar(4)) || '-10-' ||'01',date)) and (cast(cast(extract(year from current_date),varchar(4)) || '-10-' ||'31',date))) then ([Date Field]) else (null)

It works but you have to take care of null depending upon how the report is laid out.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

1. Set up your parameter at the report level as usual, check the box Multi-value, and set the Data Type to String 2. Set the Stored Procedure Parameter to varchar(max)  --<---- This part here is the key to it's success Example:    @cst_key var…
Hi, In my previous Article  (http://www.experts-exchange.com/Database/Reporting/A_15199-Introduction-to-Microstrategy.html)I discussed some basic understanding of Microstrategy that how we can get in Intro of Microstrategy (MSTR). Now it's tim…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question