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

Need help with Rolling Quarter formula

Started by zb12, 28 Jan 2011 01:38:03 PM

Previous topic - Next topic

zb12

I'm trying to develop a filter formula that will search and return information from the quarter. For instance, if the month ending is December then I want the formula to return info from the quarter( OCT,NOV, and DEC). However, if the month ending is November, then i want the formula to return the quarter to date info (OCT and NOV). I want this to work in this way throughout the whole year, only showing one quarters numbers at a time and moving on to the next quarter after the previous one has ended.

Here is what I have so far:


if(datepart({mm},[DDA Account].[Date Last Updated])in(3,6,9,12)) then ([DDA Account].[Date Opened]>=_first_of_month(_add_months(current_date,-3))) else if(datepart({mm},[DDA Account].[Date Last Updated])in(2,5,8,11)) then ([DDA Account].[Date Opened]>=_first_of_month(_add_months(current_date,-2))) else if(datepart({mm},[DDA Account].[Date Last Updated])in(1,4,7,10)) then ([DDA Account].[Date Opened]>=_first_of_month(_add_months(current_date,-1))) else null

When I run the report I get this error:
RQP-DEF-0177

       An error occurred while performing operation 'sqlPrepareWithOptions' status='-16'.

      Details 

UDA-SQL-0358 Line 2: Syntax error near ">=". RSV-SRV-0042 Trace back: RSReportService.cpp(747): QFException: CCL_CAUGHT: RSReportService::process() RSReportServiceMethod.cpp(217): QFException: CCL_RETHROW: RSReportServiceMethod::process(): promptPagingForward_Request RSASyncExecutionThread.cpp(690): QFException: RSASyncExecutionThread::checkException RSASyncExecutionThread.cpp(204): QFException: CCL_CAUGHT: RSASyncExecutionThread::run(): promptPagingForward_Request RSASyncExecutionThread.cpp(740): QFException: CCL_RETHROW: RSASyncExecutionThread::processCommand(): promptPagingForward_Request Execution/RSRenderExecution.cpp(614): QFException: CCL_RETHROW: RSRenderExecution::execute Assembly/RSDocAssemblyDispatch.cpp(251): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssembly Assembly/RSLayoutAssembly.cpp(63): QFException: CCL_RETHROW: RSLayoutAssembly::assemble Assembly/RSDocAssemblyDispatch.cpp(317): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForward Assembly/RSReportPagesAssembly.cpp(163): QFException: CCL_RETHROW: RSReportPagesAssembly::assemble Assembly/RSDocAssemblyDispatch.cpp(268): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssembly Assembly/RSPageAssembly.cpp(284): QFException: CCL_RETHROW: RSPageAssembly::assemble Assembly/RSDocAssemblyDispatch.cpp(268): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssembly Assembly/RSTableRowAssembly.cpp(154): QFException: CCL_RETHROW: RSTableRowAssembly::assemble Assembly/RSDocAssemblyDispatch.cpp(268): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssembly Assembly/RSTableCellAssembly.cpp(108): QFException: CCL_RETHROW: RSTableCellAssembly::assemble Assembly/RSDocAssemblyDispatch.cpp(317): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForward Assembly/RSAssembly.cpp(610): QFException: CCL_RETHROW: RSAssembly::createListIterator Assembly/RSAssembly.cpp(651): QFException: CCL_RETHROW: RSAssembly::createListIterator RSQueryMgr.cpp(952): QFException: CCL_RETHROW: RSQueryMgr::getListIterator RSQueryMgr.cpp(1019): QFException: CCL_RETHROW: RSQueryMgr::getResultSetIterator RSQueryMgr.cpp(1165): QFException: CCL_RETHROW: RSQueryMgr::createIterator RSQueryMgr.cpp(1476): QFException: CCL_RETHROW: RSQueryMgr::executeRsapiCommand RSQueryMgr.cpp(1463): QFException: CCL_RETHROW: RSQueryMgr::executeRsapiCommand RSQueryMgrExecutionHandlerImpl.cpp(172): QFException: CCL_RETHROW: RSQueryMgrExecutionHandlerImpl::execute() RSQueryMgrExecutionHandlerImpl.cpp(164): QFException: CCL_RETHROW: RSQueryMgrExecutionHandlerImpl::execute() RSQFSession.cpp(250): QFException: CCL_RETHROW: RSQFSession::DoRequest QFSSession.cpp(610): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest() QFSSession.cpp(608): QFException: CCL_CAUGHT: QFSSession::ProcessDoRequest() QFSSession.cpp(557): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest() QFSConnection.cpp(737): QFException: CCL_RETHROW: QFSConnection::Execute QFSQuery.cpp(117): QFException: CCL_RETHROW: QFSQuery::Execute v2 CoordinationQFSQuery.cpp(3787): QFException: CCL_THROW: CoordinationPlanner 

Where am I going wrong? Do I need to try a different approach? Is there an easier way?

charlie

You can not use a '>=' operator in the then action for your if statement. Make 2 data items, one for start_month and one for end_month.

In start_month put logic as
case
when curr_month in (1,2,3) then start_month = 1
when curr_month in (4,5,6) then start_month = 4
when curr_month in (7,8,9) then start_month = 7
when curr_month in (10,11,12) then start_month = 10
else ''
end

In end_month i think as per your requirement the curr_month will be the end_month

Then put your filter as
to_be_compared_month between start_month and end_month

hope it helps
Charlie