Print Page - Current Month Part of Date

Title: Current Month Part of Date
Post by: ertemen on 08 Sep 2010 08:36:05 AM

Hello everyone,

I am very new to Cognos and I am trying to develop a report against a cube. I am using the following expression and I get an error.
[Current_Cal_Year] = month(Current_Date).

As far as I understand it doesn't like Month bit. Are there any functions to strip certain parts of date?

Thanks advance for your help.

Thanks

Enis

Title: Re: Current Month Part of Date
Post by: MFGF on 08 Sep 2010 10:13:11 AM

Hi,

We need a little more information to be able to help.

Where are you using your expression - in a detail filter or a query calculation?

What type of item is [Current_Cal_Year] - is it a hierarchy, a level, a member, a set, an attribute or something else you have calculated in the report?

Thanks,

MF.

Title: Re: Current Month Part of Date
Post by: Lynn on 08 Sep 2010 11:34:45 AM

Posting error message details is also useful

Title: Re: Current Month Part of Date
Post by: tupac_rd on 10 Sep 2010 11:55:59 AM

does this work
[Current_Cal_Year] = extract(current_date,month)

Title: Re: Current Month Part of Date
Post by: ksvchowdary on 10 Sep 2010 12:38:54 PM

use like this : extract(month , Current_Date)
ex: if Current_Date = 2010-09-10 12:24:22 , it will extract only 10 ( September).

like you can extract year , day ...etc..

Best Regards,
**********
Sri Kalyanapu

Title: Re: Current Month Part of Date
Post by: ertemen on 22 Sep 2010 05:09:23 AM

Hi All,

thanks for all your responses. I could not wirte as I was away for a while. What I'm trying to do is to calculate number of transactions in the current week, month to date and year to date. Package is relational package, creating query calculation and the column is an attribute.

Basically what I am doing is in the query calculation
If ([FB Transactions].[dim_date].[current_cal_week] = extract(Current_date,week) )then
(count ([FB Transactions].[fact_FBSales].[transactionID]))

for filter the number of transactions for this week only. This is the calculation for the first column obviously.

I also tried this which yield the same error

If ([FB Transactions].[dim_date].[current_cal_week] = extract(Current_date,week) )then
(count ([FB Transactions].[fact_FBSales].[transactionID]))
else
(0)

error that I am getting is to do with parsing around the extract function. I swapped week and current_date in the function and still get the same error.

Any ideas?

Title: Re: Current Month Part of Date
Post by: ertemen on 22 Sep 2010 05:19:26 AM

Hi Guys,

found out what the problem was and wanted to update the forum in case someone else is experiencing same sort of problem.

first of all extract(MONTH,current_date) gives month and extract(YEAR,current_date) gives the year. Please note that it is case sensitive.

For the current week, there is a built in function called _week_of_year which takes a date parameter.

If ([FB Transactions].[dim_date].[current_cal_week] = _week_of_year (Current_date)) then
(count ([FB Transactions].[fact_FBSales].[transactionID]))
else
(0)

COGNOiSe.com - The IBM Cognos Community

IBM Cognos 8 Platform => COGNOS 8 => Report Studio => Topic started by: ertemen on 08 Sep 2010 08:36:05 AM