Current Month Part of Date

[ertemen]

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

[MFGF]

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.

[Lynn]

Posting error message details is also useful

[tupac_rd]

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

[ksvchowdary]

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

[ertemen]

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?

[ertemen]

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)