Report Studio :Cognos Time and Date Functions
Cognos date and time functions
Date and Time Functions
|
|
cast(extract(year,current_ timestamp),varchar(4))
|
>>> 2014
|
extract(hour,current_timestamp)
|
>>> 10
|
extract(day,current_timestamp)
|
>>> 13
|
cast(extract(month,current_ timestamp),varchar(2))
|
>>> 7
|
cast(current_timestamp,varchar(50))
|
>>> 2012-07-13 10:44:32.991000000+04:70
|
cast(extract(hour,current_ timestamp),varchar(2))
|
>>> 10
|
Last Day of Current Month
|
_last_of_month (current_timestamp)
|
Date Minus 24 Hours
|
_add_days ((current_timestamp),-1)
|
Extract
minute as two digit alpha:
|
case
(CAST(extract ( minute, current_timestamp), VARCHAR(2)))
when
'0' then ('00')
when
'1' then ('01')
when
'2' then ('02')
when
'3' then ('03')
when
'4' then ('04')
when
'5' then ('05')
when
'6' then ('06')
when
'7' then ('07')
when
'8' then ('08')
when
'9' then ('09')
else
(CAST(extract ( minute, current_timestamp), VARCHAR(2)))
end
|
get prior month based on current month
|
extract(month,_add_months(
current_timestamp,-1))
|
This
CASE function extracts the first two characters of the current date and
translates it
into
a financial period:
|
CASE
(substring(cast(current_ timestamp,varchar(50)),6,2))
WHEN
'07' THEN '04'
WHEN
'08' THEN '05'
WHEN
'09' THEN '06'
WHEN
'10' THEN '07'
WHEN
'11' THEN '08'
WHEN
'12' THEN '09'
WHEN
'01' THEN '10'
WHEN
'02' THEN '11'
WHEN
'03' THEN '12'
WHEN
'04' THEN '01'
WHEN
'05' THEN '02'
WHEN
'06' THEN '03'
ELSE
'14'
END
|
Calculate
the financial year based on the current date
|
Apr-Mar
if
(extract(month,current_timestamp)♥)
then
(
timestamp)-1,varchar(4))+'-'+
timestamp),varchar(4))
)
else
(
timestamp),varchar(4))+'-'+
timestamp)+1,varchar(4))
)
Dec-Nov
if
(extract(month,current_timestamp)<11)
then
(
timestamp)-1,varchar(4))+'-'+
timestamp),varchar(4))
)
else
(
timestamp),varchar(4))+'-'+
timestamp)+1,varchar(4))
)
|
First day of this year | _add_days(Current_date ; (_day_of_year(Current_date)*-1)+1 ) |
First day of last year (only used in january): | _first_of_month(_add_years(current_date;-1)) OR _last_of_month(_add_months (current_date;-1)) |
date-to-string
|
substring(cast(extract(month,[Project Start Date]),VARCHAR(10)),1,2) + '/' + substring(cast(extract(year,[Project Start Date]),VARCHAR(10)),3,2) + ' - ' + substring(cast(extract(month,[Project End Date]),VARCHAR(10)),1,2) + '/' + substring(cast(extract(year,[Project End Date]),VARCHAR(10)),3,2)
OR substring(cast(extract(month,[Project Start Date]),VARCHAR(10)),1,2) + '/' + substring(cast(extract(year,[Project Start Date]),VARCHAR(10)),3,2) + ' - ' + substring(cast(extract(month,[Project End Date]),VARCHAR(10)),1,2) + '/' + substring(cast(extract(year,[Project End Date]),VARCHAR(10)),3,2) |
CAST and EXTRACT Functions:
Where [Time stamp] = Jun 8, 2009
5:26:47 PM
Expression
|
Result
|
cast(extract( year, [Time stamp]), varchar(4))
|
2009 (alpha)
|
extract( hour, [Time stamp])
|
17 (numeric)
|
extract( day, [Time stamp])
|
8 (numeric)
|
cast(extract(month,[Time stamp]),VARCHAR(2))
|
6 (alpha – note lack of leading zero)
|
cast([Time stamp], date)
|
Jun 8, 2009 (‘date’ is a data type)
|
cast ([Time stamp], varchar(50))
|
2009-06-08 00:00:00.000000000
|
cast(extract (hour,[Time stamp]), VARCHAR(2))
|
5 (alpha)
|
Extract minute as two digit alpha:
case (CAST(extract ( minute,
[Audit].[Run Reports].[Time stamp]), VARCHAR(2)))
when '0' then ('00')
when '1' then ('01')
when '2' then ('02')
when '3' then ('03')
when '4' then ('04')
when '5' then ('05')
when '6' then ('06')
when '7' then ('07')
when '8' then ('08')
when '9' then ('09')
else (CAST(extract ( minute,
[Audit].[Run Reports].[Time stamp]), VARCHAR(2)))
end
or
TO_CHAR([Time stamp], ‘MM’)
Last Day of Current Month:
_last_of_month(date2timestamp(Today()))
Date Minus 24 Hours:
_add_days([Audit].[COGIPF_RUNREPORT].[TIME
STAMP],-1)
Use a date in a filter:
Use a date in a filter:
[Audit].[Run Reports].[Time stamp]
> cast('2010-05-01', timestamp)
or
cast ([CURRENT_HIRE_DATE], varchar(50))
> '2005-11-10 00:00:00.000000000'
Note: Strangely, [Audit].[Run Reports].[Time stamp] = cast('2010-05-01', timestamp)
does not work as a filter. However,
the following does work:
Between Function:
Between Function:
[Time stamp] between (cast('2010-05-10', timestamp)) and (cast('2010-05-11', timestamp))
To restrict a report based on a hard coded date (ex. 5/10/2010):
cast ([Time stamp], varchar(50)) = '2010-05-10 00:00:00.000000000'
To filter records based on dates in prior month (based on system date):
extract(month, _add_months(current_date, -1)) = cast(extract(month,[Time stamp]),varchar(2))
To filter records based on a timestamp when using date prompts on a prompt page:
Filter1: [TIME STAMP] >= cast((?beginDate?) as TIMESTAMP)
Filter2: [TIME STAMP] <= _add_days(cast((?endDate?) as TIMESTAMP),1)
(A day is added to the end date to allow for the use of a single day range. That is, the
beginDate will be Apr 27, 2012
12:00:00 AM if April 27 is selected. If April 27 is also
selected as the end date, 24 hours
must be added so that the end date used in the filter
is Apr 28, 2012 12:00:00 AM)
SYSDATE:
This CASE function extracts the first
three characters of the current date and translates it
into a fiscal period:
CASE (substr({sysdate},4,3))
WHEN 'JUL' THEN '01'
WHEN 'AUG' THEN '02'
WHEN 'SEP' THEN '03'
WHEN 'OCT' THEN '04'
WHEN 'NOV' THEN '05'
WHEN 'DEC' THEN '06'
WHEN 'JAN' THEN '07'
WHEN 'FEB' THEN '08'
WHEN 'MAR' THEN '09'
WHEN 'APR' THEN '10'
WHEN 'MAY' THEN '11'
WHEN 'JUN' THEN '12'
ELSE '14'
END
To calculate the fiscal year based on the current date (fiscal year for 2009/2010 is 2010):
IF (extract( month, {sysdate}) < 7)
THEN (cast(extract(year, {sysdate}),
varchar(4)))
ELSE (cast(extract(year, {sysdate})+1,
varchar(4)))
This function will return the date of the same day of the week a year ago and controls for
leap years. For example, if today is
Tuesday, 07/31/2012 then the function returns
08/02/2012 (Tuesday) – trunc removes
the time portion of the date value:
trunc(_add_days({sysdate},-364))
TO_CHAR:
General format is TO_CHAR(datetime, ‘format
element’)
Example: TO_CHAR(current_date, ‘YYYYMM’)
= 201012 (December, 2010)
Element Description
D Number of day of the week
DD Number of day of the month
DAY Name of the day (ex. FRIDAY)
HH Hour of day (1-12)
HH24 Hour of day (0-23)
MI Minute (0-59)
MM Month (January = 01…December = 12)
MONTH Name of month
MON Abbreviated name of month
SS Seconds
WW Week of year (1-53)
W Week of month (1-5 where week 1 starts
on first day of month and ends
on seventh)
YYYY 4-digit year (YY returns last two
digits of 4-digit year)
Year and Month in YYYYMM format:
Current year/month –
to_char(current_date,'YYYYMM') ex: 200910
Previous month -
to_char(add_months(current_date,-1),'YYYYMM') ex: 200909
Previous year/month-
to_char(add_months(current_date,-13),'YYYYMM') ex: 200809
OTHER
Age in years:
_age([BENEFICIARY_BIRTH_DATE]) / 100
_age([BENEFICIARY_BIRTH_DATE]) / 100
Age in years:
_years_between(current_date,[Date of Birth]) returns number of full years between the two dates
_years_between(current_date,[Date of Birth]) returns number of full years between the two dates
Age at time of graduation in years and fractional months:
_months_between ([OUTCOME_GRADUATION_DATE],[BIRTH_DATE])/12
returns
number of full years and fractional
months (ex: 21.4) as of the date of graduation
Previous month:
extract(month, _add_months(current_date, -1))
Add/subtract months:
'20' || substrb(to_char(_add_months([Time stamp],-24)),8,2)
– This will subtract 24 months from the Time stamp month and display the resulting
year.
TRUNC (supplied by H. Cleveland)
TRUNC(date, [format])
Where [format] is optional and can be
any of the following:
Year SYYYY, YYYY, YEAR, SYEAR, YYY, YY, Y
ISO Year IYYY, IY, I
Quarter Q
Month MONTH, MON, MM, RM
Week WW
IW IW
W W
Day DDD, DD, J
Start day of the week DAY, DY, D
Hour HH, HH12, HH24
Minute MI
Examples:
Functions
|
Sytax
|
Start of today
|
trunc({sysdate})
|
Start of yesterday
|
(trunc(_add_days({sysdate}, -1),'dd'))
|
Start of Current Month
|
trunc({sysdate},'mm')
|
End of Current Month
|
last_day({sysdate})
|
Start of Previous Month
|
trunc((trunc({sysdate},'mm')-1),'mm')
|
End of Previous Month
|
trunc({sysdate},'mm')-1
|
Start of Current Quarter
|
trunc({sysdate},'q')
|
End of Current Quarter
|
add_months(trunc({sysdate},'q'),3)-1
|
Start of Previous Quarter
|
trunc(trunc({sysdate},'q')-1,'q')
|
End of Previous Quarter
|
trunc({sysdate},'q')-1
|
Start of Current Year
|
trunc({sysdate},'y')
|
End of Current Year
|
add_months(trunc({sysdate},'y'),12)-1
|
Start of Previous Year
|
trunc(trunc({sysdate},'y')-1,'y')
|
End of Previous Year
|
trunc({sysdate},'y')-1 When [TIME STAMP] = Aug 3, 2010 5:05:45 PM
|
Start of day in [TIME STAMP]
|
trunc(_add_days([TIME STAMP], 0),'dd') = Aug 3, 2010 12:00:00 AM
|
Start of day previous to day in [TIME STAMP]
|
trunc(_add_days([TIME STAMP],-1),'dd') = Aug 2, 2010 12:00:00 AM
|
Start of the hour in [TIME STAMP]
|
trunc([TIME STAMP], 'hh') = Aug 3, 2010 5:00:00 PM
|
All records that occurred yesterday
|
[TIME STAMP] between (trunc(_add_days({sysdate}, -1),'dd')) and (trunc({sysdate}))
|
Day of week for current date
|
_day_of_week(current_date , 1)
|
If it's Monday, include Decision Dates = Sat, Sun or Mon otherwise DECISION DATE = Current Date:
|
(((_day_of_week(current_date,1) = 1 ) AND
([Admission Application].[Admissions Application].[LATEST_DECISION_DATE]
BETWEEN (current_date - 2) AND current_date))
OR ((_day_of_week(current_date,1) <> 1) AND
([Admission Application].[Admissions Application].[LATEST_DECISION_DATE] =
current_date)))
If it’s Monday, then include any record beginning with the start of Friday through the start
of Monday, otherwise include any record beginning with the start of yesterday through
|
the start of Today:
|
((_day_of_week(current_date,1) = 1 ) AND
([Audit].[COGIPF_RUNREPORT].[TIME STAMP] BETWEEN
(trunc(_add_days({sysdate}, -3),'dd')) AND (trunc({sysdate})))
OR
((_day_of_week(current_date,1) <> 1) AND
([Audit].[COGIPF_RUNREPORT].[TIME STAMP] BETWEEN
(trunc(_add_days({sysdate}, -1),'dd')) AND (trunc({sysdate})))))
|
TRUNC (timestamp, [parts of
timestamp])
Where [parts of timestamp] can be
'D'-- Return only day information in
the timestamp. Hours, minutes, and seconds are
returned as zero.
'h'-- Return only day and hour
information in the timestamp. Minutes and seconds are
returned as zero.
'm'-- Return only day, hour, and
minute information in the timestamp. Seconds are
returned as zero.
's'-- Return only day, hour, and second
information in the timestamp, but do not show
milliseconds.
TRUNC also can be used with decimal
numbers to return a number rounded to a given number
of decimal places.
For example:
For example:
TRUNC(1234.567) returns 1,234
TRUNC(1234.567, 1) returns 12,345.6
TRUNC(1234.567, -2) returns 1,200
Date related relational functions
_last_of_month (_add_months (_add_days ([Fact].[Dim Time].[Current Date],1),-1))
case when ?Relative Date?='7d'
then ( [Date] between _add_days(2007-05-17,-7) and (2007-05-17))
when ?Relative Date? ='MTD'
then( [Date] between _first_of_month(2007-05-17) and (2007-05-17))
when ?Relative Date? ='YTD'
then( [Date] between _add_days(2007-05-17,((_day_of_year(2007-05-17)-1)*-1))
and (2007-05-17))
Current YTD Sales:
if ( SalesDate between _make_timestamp(extract(year,current_date), 1, 1) and current_date )
then (SalesAmount)
else (0)
Prior YTD Sales:
if ( SalesDate between _make_timestamp((extract(year,current_date)-1), 1, 1) and _add_years(current_date,-1) )
then (SalesAmount)
else (0)
Get year start date
1)-_day_of_year ([End Date])
-366
2)_add_days ([End Date], -_day_of_year ([End Date])+1)
1-Jan-12 12:00:00 AM
Get last complete Month date
1)[Fact].[Dim Time].Current Date]
6-Jan-13 12:00:00 AM
2)_add_days ([Fact].[Dim Time].Current Date],1)
7-Jan-13 12:00:00 AM
3)_add_months (_add_days ([Fact].[Dim Time].Current Date],1),-1)
7-Dec-12 12:00:00 AM
4)_last_of_month (_add_months (_add_days ([Fact].[Dim Time].Current Date],1),-1))
31-Dec-12 12:00:00 AM
[Fact Table].[Dim Time].[Month Id] >=
(year(_add_months (_add_days ([Fact Table].[Dim Time].[Current Date],1),-13)) * 100) +
month(_add_months (_add_days ([Fact Table].[Dim Time].[Current Date],1),-13))and
[Fact Table].[Dim Time].[Month Id] <=
(year(_add_months (_add_days ([Fact Table].[Dim Time].[Current Date],1),-1)) * 100) +
month(_add_months (_add_days ([Fact Table].[Dim Time].[Current Date],1),-1))
cast(cast (extract (year,[Returned items (query)].[Time dimension].[Date]), Char
(4))+substring (cast(cast(_round (((extract (month,[Returned items (query)].[Time
dimension].[Date])/100)+1)*100,0),integer),Char(3)),2,2)+substring (cast(cast
(_round (((extract (day,[Returned items (query)].[Time dimension].[Date])/100)+1)
*100,0),integer),Char(3)),2,2),integer)
string2date (
substring (number2string ([Query1].[Date]),1,4)
+'-'+
substring (number2string ([Query1].[Date]),5,2)
+'-'+
substring (number2string ([Query1].[Date]),7,2)
)
For IBM DB2 the calculation should look like this:
For Microsoft SQLServer the calculation should look like this:
For Oracle:
[monthClosingPeriod] =
closingPeriod( [GOC].[Years].[Years].[Month] )
[periodsToDateForClosingPeriodMonth] =
PeriodsToDate( [GOC].[Years].[Years].[Year] , [monthClosingPeriod] )
how to turn current day to real date, but dynamically displayed.
Current Day_Date
descendants ([Current Day],1)
IBM Cognos 8 Framework Manager - Dimensional Modeling for Time Period Analysis
1. Get total reminding to go for product Category XYZ, set 0 if the reminding to go as 0
if (([Product Category] = 'XYZ')
and (total ([Forecast] for [Province Id], [Product Category] )
- total([Sales] for [Province Id], [Product Category]) > 0))
then
( total ([Forecast] for [Province Id], [Product Category] )
- total([Sales] for [Province Id], [Product Category] ))
else (0)
2. TYD, QTD and HTD based day ( Assume [Fact Current Date],[Fact Current Quarter Number] and [Fact Current Quarter Number] )
#/* YTD at the begining of year*/#
_add_days ([End Date], -_day_of_year ([Fact Table].[Dim Time].[Fact Current Date])+1)
#/* QTD */#
if ([Fact Table].[Dim Time].[Quarter Id] = [Fact Table].[Dim Time].[Fact Current Year]*10+[Fact Table].[Dim Time].[Fact Current Quarter Number])
then ([Fact Table].[Fact Table].[Measure])
else (0)
#/* HTD */#
If ( [Fact Table].[Dim Time].[Fact Current Quarter Number] > 2 ) Then
( If ( [Fact Table].[Dim Time].[Quarter Number] > 2) Then
( If ([Fact Table].[Dim Time].[Quarter Id] <= [Fact Table].[Dim Time].[Fact Current Year]*10+[Fact Table].[Dim Time].[Fact Current Quarter Number])
Then [Fact Table].[Fact Table].[Measure])
else (0) )
Else ( 0) )
Else (
If ([Fact Table].[Dim Time].[Quarter Id]<=[Fact Table].[Dim Time].[Fact Current Year]*10+[Fact Table].[Dim Time].[Fact Current Quarter Number])
then ([Fact Table].[Fact Table].[Measure])
else (0) )
How to handle date related relational and dimensional functions
Make timestamp from date key
Parameters map is a good solution to get current date; however, it present challenges to directly use date type, instead, data key as integer is easily to handle. In some cases, it is requested to turn it into real date in report. Code below can help
_make_timestamp (
cast(#$CurrentDateKey{'Fact TableX'}# /10000 ,integer),
cast(#$CurrentDateKey{'Fact TableX'}# /100, integer) - ( cast(#$CurrentDateKey{'Fact TableX'}# /10000, integer))*100,
#$CurrentDateKey{'Fact TableX'}# - cast((#$CurrentDateKey{'Fact TableX'}# /100),integer)*100 )
_make_timestamp (
cast(#$CurrentDateKey{'Fact TableX'}# /10000 ,integer),
cast(#$CurrentDateKey{'Fact TableX'}# /100, integer) - ( cast(#$CurrentDateKey{'Fact TableX'}# /10000, integer))*100,
#$CurrentDateKey{'Fact TableX'}# - cast((#$CurrentDateKey{'Fact TableX'}# /100),integer)*100 )
Get last complete month date
Date + 1 Day - 1 Month, then get last day of month.
_last_of_month (_add_months (_add_days ([Fact].[Dim Time].[Current Date],1),-1))
Make week start day
For report with weeks, it is requested to display the first day of each week. The data item can get first day of each week.
case month(minimum([Dim Time].[Date] for [Dim Time].[Week Id]))
WHEN 1 THEN 'Jan'
WHEN 2 THEN 'Feb'
WHEN 3 THEN 'Mar'
WHEN 4 THEN 'Apr'
WHEN 5 THEN 'May'
WHEN 6 THEN 'Jun'
WHEN 7 THEN 'Jul'
WHEN 8 THEN 'Aug'
WHEN 9 THEN 'Sep'
WHEN 10 THEN 'Oct'
WHEN 11 THEN 'Nov'
WHEN 12 THEN 'Dec'
END
+
' ' + right('0' + cast(day(minimum([Dim Time].[Date] for [Dim Time].[Week Id])), varchar(2)), 2)
case month(minimum([Dim Time].[Date] for [Dim Time].[Week Id]))
WHEN 1 THEN 'Jan'
WHEN 2 THEN 'Feb'
WHEN 3 THEN 'Mar'
WHEN 4 THEN 'Apr'
WHEN 5 THEN 'May'
WHEN 6 THEN 'Jun'
WHEN 7 THEN 'Jul'
WHEN 8 THEN 'Aug'
WHEN 9 THEN 'Sep'
WHEN 10 THEN 'Oct'
WHEN 11 THEN 'Nov'
WHEN 12 THEN 'Dec'
END
+
' ' + right('0' + cast(day(minimum([Dim Time].[Date] for [Dim Time].[Week Id])), varchar(2)), 2)
Half to date current year, prior year
When it is requested to get Half to date, get reference date first as data item, then get date started. CYTD- Current year to date; PYTD- Prior year to date; CHTD-Current half year to date; PHTD-Prior half year to date
[Dim Time].[Date] between [DateFrom] and [DateTo]
[DateFrom]
case when ([Current Month Number] >6 ) and (?TimePeriodGrp?='CHTD' or ?TimePeriodGrp?='PHTD') then _make_timestamp (year([DateTo]),07,01)
else _make_timestamp (year([DateTo]),01,01)
End
[DateTo]
case
when (?TimePeriodGrp?='CYTD') then ([Dim Time (Activity)].[ Current Date])
when (?TimePeriodGrp?='PYTD') then ( _add_years ([Dim Time (Activity)].[ Current Date], -1))
End
{ it also can be done by directly using filter
( ([Fact].[Dim Time].[Current Month Number] <7) and ([Fact].[Dim Time].[Year] = [Fact].[Dim Time].[Current Year] ) ) OR ( ([Fact].[Dim Time].[Current Month Number] >=7) and ([Fact].[Dim Time].[Year] = [Fact].[Dim Time].[Current Year] ) and [Fact].[Dim Time].[Month Number] >=7)
}
{ it also can be done by directly using filter
( ([Fact].[Dim Time].[Current Month Number] <7) and ([Fact].[Dim Time].[Year] = [Fact].[Dim Time].[Current Year] ) ) OR ( ([Fact].[Dim Time].[Current Month Number] >=7) and ([Fact].[Dim Time].[Year] = [Fact].[Dim Time].[Current Year] ) and [Fact].[Dim Time].[Month Number] >=7)
}
7d, MTD, YTD, PYTD
case when ?Relative Date?='7d'
then ( [Date] between _add_days(2007-05-17,-7) and (2007-05-17))
when ?Relative Date? ='MTD'
then( [Date] between _first_of_month(2007-05-17) and (2007-05-17))
when ?Relative Date? ='YTD'
then( [Date] between _add_days(2007-05-17,((_day_of_year(2007-05-17)-1)*-1))
and (2007-05-17))
Current YTD Sales:
if ( SalesDate between _make_timestamp(extract(year,current_date), 1, 1) and current_date )
then (SalesAmount)
else (0)
Prior YTD Sales:
if ( SalesDate between _make_timestamp((extract(year,current_date)-1), 1, 1) and _add_years(current_date,-1) )
then (SalesAmount)
else (0)
Get Last month of data available
assume available date is [AvailableDate] and time dimension is [DimTime]: (year(_add_months (_add_days ([Namespace].[DimTime].[ AvailableDate],1),-1)) * 100) + month(_add_months (_add_days ([Namespace].[ DimTime].[ AvailableDate],1),-1))
Get start date and end date based on last available month
Get year start date
1)-_day_of_year ([End Date])
-366
2)_add_days ([End Date], -_day_of_year ([End Date])+1)
1-Jan-12 12:00:00 AM
Get last complete Month date
1)[Fact].[Dim Time].Current Date]
6-Jan-13 12:00:00 AM
2)_add_days ([Fact].[Dim Time].Current Date],1)
7-Jan-13 12:00:00 AM
3)_add_months (_add_days ([Fact].[Dim Time].Current Date],1),-1)
7-Dec-12 12:00:00 AM
4)_last_of_month (_add_months (_add_days ([Fact].[Dim Time].Current Date],1),-1))
31-Dec-12 12:00:00 AM
Get last 13 Months
[Fact Table].[Dim Time].[Month Id] >=
(year(_add_months (_add_days ([Fact Table].[Dim Time].[Current Date],1),-13)) * 100) +
month(_add_months (_add_days ([Fact Table].[Dim Time].[Current Date],1),-13))and
[Fact Table].[Dim Time].[Month Id] <=
(year(_add_months (_add_days ([Fact Table].[Dim Time].[Current Date],1),-1)) * 100) +
month(_add_months (_add_days ([Fact Table].[Dim Time].[Current Date],1),-1))
Show date in crosstab
cast(cast (extract (year,[Returned items (query)].[Time dimension].[Date]), Char
(4))+substring (cast(cast(_round (((extract (month,[Returned items (query)].[Time
dimension].[Date])/100)+1)*100,0),integer),Char(3)),2,2)+substring (cast(cast
(_round (((extract (day,[Returned items (query)].[Time dimension].[Date])/100)+1)
*100,0),integer),Char(3)),2,2),integer)
string2date (
substring (number2string ([Query1].[Date]),1,4)
+'-'+
substring (number2string ([Query1].[Date]),5,2)
+'-'+
substring (number2string ([Query1].[Date]),7,2)
)
For IBM DB2 the calculation should look like this:
cast_integer(cast_char (Year([Returned items (query)].[Time dimension].[Date]) ,4)
||
right( replace ('0'||cast_char (Month([Returned items (query)].[Time dimension].
[Date]) ,2),' ',''),2)
||
right(replace ('0'||cast_char (day([Returned items (query)].[Time dimension].[Date]) ,
2),' ',''),2))
For Microsoft SQLServer the calculation should look like this:
cast_integer(convert({varchar},[Returned items (query)].[Time dimension].[Date],112))
For Oracle:
to_number(To_Char([Returned items (query)].[Time dimension].[Date], 'YYYYMMDD'))
Useful links
Cognos Time and Date Functions from temple universityFind Current Period using closingPeriod Function
closingPeriod ([sales_and_marketing_cs].[Time].[Time].[Month])
Find Current Period by Filtering on Measure Data
item(tail(filter(members([sales_and_marketing].[Time].[Time].[Month]), tuple([Revenue], currentMember([sales_and_marketing].[Time].[Time])) is not null), 1), 0)
Check link for detail explanation http://www.ibm.com/developerworks/data/library/cognos/reporting/dimensional_queries/page561.html
Check link for detail explanation http://www.ibm.com/developerworks/data/library/cognos/reporting/dimensional_queries/page561.html
Current Month/Quarter/YTD/ Trailing Twelve Months
Often use a “base” month current period to build everything else off of:
[Month Current] = closingPeriod( [goc].[Years].[Years].[Month] )
Quarter
parent( [Month Current] )
YTD
total( currentMeasure within set
periodsToDate( [goc].[Years].[Years].[Year], [Month Current] ) )
Trailing Twelve Months
total( currentMeasure within set
lastPeriods( 12, [Month Current] ) )
Month % Change Prior Year
( [Month Current] - [Month Prior Year] ) / [Month Prior Year]
Where [Month Prior Year] = lag( [Month Current] , 12 )
Note: currentMeasure is in the context of the crosstab
[Month Current] = closingPeriod( [goc].[Years].[Years].[Month] )
Quarter
parent( [Month Current] )
YTD
total( currentMeasure within set
periodsToDate( [goc].[Years].[Years].[Year], [Month Current] ) )
Trailing Twelve Months
total( currentMeasure within set
lastPeriods( 12, [Month Current] ) )
Month % Change Prior Year
( [Month Current] - [Month Prior Year] ) / [Month Prior Year]
Where [Month Prior Year] = lag( [Month Current] , 12 )
Note: currentMeasure is in the context of the crosstab
Period to date
[monthClosingPeriod] =
closingPeriod( [GOC].[Years].[Years].[Month] )
[periodsToDateForClosingPeriodMonth] =
PeriodsToDate( [GOC].[Years].[Years].[Year] , [monthClosingPeriod] )
Show real date from power cube
how to turn current day to real date, but dynamically displayed.
Current Day_Date
descendants ([Current Day],1)
Yesterday_Date
descendants ([Yesterday],1)
descendants ([Yesterday],1)
Useful links
Using the periodsToDate function within IBM Cognos 10 Report Studio to Calculate an OLAP Running-TotalIBM Cognos 8 Framework Manager - Dimensional Modeling for Time Period Analysis
Oracle date functions
Get the last day of the week
select trunc(sysdate, 'DAY')+6 from dual;
Get first day of year SELECT TRUNC(TO_DATE('17-DEC-2001'),'YEAR') "First Day" FROM Dual; Get last Day of Current Month
SELECT add_months(trunc(sysdate) - (to_number(to_char(sysdate,'DD')) - 1), 1) -1 FROM dual
Get first day of year SELECT TRUNC(TO_DATE('17-DEC-2001'),'YEAR') "First Day" FROM Dual; Get last Day of Current Month
SELECT add_months(trunc(sysdate) - (to_number(to_char(sysdate,'DD')) - 1), 1) -1 FROM dual
SQL server date functions
Get the first day of month
DATEADD(dd,-(DAY(DT.DAY_DT)-1),DT.DAY_DT)
Get date key
select cast(convert(varchar(8), getdate(), 112) as integer)
Get last Sunday
Select cast(convert(varchar(8), dateadd(day,-datepart(weekday,dateadd(WEEK,-1,GETDATE()))+1,dateadd(WEEK,-1,GETDATE())), 112) as integer)
Select cast(convert(varchar(8), dateadd(day,-datepart(weekday,dateadd(WEEK,-1,GETDATE()))+1,dateadd(WEEK,-1,GETDATE())), 112) as integer)
Useful Query items
2. TYD, QTD and HTD based day ( Assume [Fact Current Date],[Fact Current Quarter Number] and [Fact Current Quarter Number] )
#/* YTD at the begining of year*/#
_add_days ([End Date], -_day_of_year ([Fact Table].[Dim Time].[Fact Current Date])+1)
#/* QTD */#
if ([Fact Table].[Dim Time].[Quarter Id] = [Fact Table].[Dim Time].[Fact Current Year]*10+[Fact Table].[Dim Time].[Fact Current Quarter Number])
then ([Fact Table].[Fact Table].[Measure])
else (0)
#/* HTD */#
If ( [Fact Table].[Dim Time].[Fact Current Quarter Number] > 2 ) Then
( If ( [Fact Table].[Dim Time].[Quarter Number] > 2) Then
( If ([Fact Table].[Dim Time].[Quarter Id] <= [Fact Table].[Dim Time].[Fact Current Year]*10+[Fact Table].[Dim Time].[Fact Current Quarter Number])
Then [Fact Table].[Fact Table].[Measure])
else (0) )
Else ( 0) )
Else (
If ([Fact Table].[Dim Time].[Quarter Id]<=[Fact Table].[Dim Time].[Fact Current Year]*10+[Fact Table].[Dim Time].[Fact Current Quarter Number])
then ([Fact Table].[Fact Table].[Measure])
else (0) )
Time filter
filter(
[Time Dimension].[Time Hierarchy].[Date],
roleValue('_businessKey', currentMember([Time Dimension].[Time Hierarchy])) >= #sq(prompt('From', 'Date'))# and
roleValue('_businessKey', currentMember([Time Dimension].[Time Hierarchy])) <= #sq(prompt('To', 'Date'))#
)
[Time Dimension].[Time Hierarchy].[Date],
roleValue('_businessKey', currentMember([Time Dimension].[Time Hierarchy])) >= #sq(prompt('From', 'Date'))# and
roleValue('_businessKey', currentMember([Time Dimension].[Time Hierarchy])) <= #sq(prompt('To', 'Date'))#
)
Identifying nth Weekday of a month in reports
Requirement: Identify the 2nd Thursday of the current month. Solution: Create data items ToDate, 1stDateofMonth, 1stDayofMonth, Nth, Day , AddDays, NthDay ToDate - current_date 1stDateofMonth - _first_of_month(ToDate) 1stDayofMonth - _day_of_week(1stDateofMonth ,1) // Assuming Monday is Day 1 Nth - 2 // The nth value of the weekday required, in our case we require 2nd Thursday Day - 4 // Assuming Monday is Day 1, then Thursday is Day 4 AddDays - case when [Day] >= [1stDayofMonth] then [Day] - [1stDayofMonth] else ([Day] + [1stDayofMonth]) -1 end NthDay - _add_days([1stDateofMonth],(([Nth]-1) * 7 ) + [AddDay]) NthDay returns the 2nd Thursday of the month.Age calculation
We have two ways to calculate age in years and age in yy years mm months and dd days format
Calculate Age in years...
Create a query Calculation as below...
_years_between (current_date, [Birth date column])
Calculate Age in yy years, mm months and dd days format...
(replace [Birth Date] with your own Data of birth column)
substring (cast(_ymdint_between (current_date, [Birth date]),char(6)),1,2) || ' Years '||
substring (cast(_ymdint_between (current_date, [Birth date]),char(6)),3,2)
|| ' months ' ||
substring (cast(_ymdint_between (current_date, [Birth date]),char(6)),5,2)
|| ' Days '