Thursday, October 18, 2012

How to handle date related relational and dimensional functions

Date related relational 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 )

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 10 THEN 'Oct'
WHEN 11 THEN 'Nov'
WHEN 12 THEN 'Dec'
' ' + 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]


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)




when (?TimePeriodGrp?='CYTD') then ([Dim Time (Activity)].[ Current Date])

when (?TimePeriodGrp?='PYTD') then ( _add_years ([Dim Time (Activity)].[ Current Date], -1))


{ 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

-_day_of_year ([End Date])

_add_days ([End Date], -_day_of_year ([End Date])+1)
1-Jan-12 12:00:00 AM

Get last complete Month date

[Fact].[Dim Time].Current Date]
6-Jan-13 12:00:00 AM

_add_days ([Fact].[Dim Time].Current Date],1)
7-Jan-13 12:00:00 AM

_add_months (_add_days ([Fact].[Dim Time].Current Date],1),-1)
7-Dec-12 12:00:00 AM

_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)

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 university

Date related dimensional functions

Please load report specification for detail

Find 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

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] )

parent( [Month Current] )

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)

descendants ([Yesterday],1)

Useful links

Using the periodsToDate function within IBM Cognos 10 Report Studio to Calculate an OLAP Running-Total

IBM 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

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)

Useful Query items

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) )

Time 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'))#

Other useful tips and tricks

Display Report Run Times
layout calculation:   Now() - AsOfNow()

Promote an individual report
Open the file ReportX.txt in a text editor such as Notepad and do the following:
a.Click Edit | Select All to highlight all the text in the file.
b.Click Edit | Copy to copy all the text in the file to the clipboard.
In Report Studio, click Tools | Open Report from Clipboard.

Extract Level
,(INSTR('/Level1/Level2/Level3/Level4/Level5/'||'/','/',1,2)-INSTR ('/Level1/Level2/Level3/Level4/Level5/'||'/','/',1,1)-1)
,(INSTR('/Level1/Level2/Level3/Level4/Level5/'||'/','/',1,3)-INSTR ('/Level1/Level2/Level3/Level4/Level5/'||'/','/',1,2)-1)
,(INSTR('/Level1/Level2/Level3/Level4/Level5/'||'/','/',1,4)-INSTR ('/Level1/Level2/Level3/Level4/Level5/'||'/','/',1,3)-1)

Transform year quarter to date

to_date(decode(?Quarter?, 'Q1', '03/31/', 'Q2', '06/30/', 'Q3', '09/30/', 'Q4', '12/31/')||?Year?, 'MM/DD/YYYY')

Useful links:
Disallow a user to send reports by e-mail
Automatically refreshing a report
What are the differences between DQM and CQE mode in IBM Cognos 10?
Bridge Query Subjects (only supported using DQM)
Working with Dynamic Filtering


No comments:

Post a Comment