Examples of level current period expressions

Some common examples of level current period expressions are defined in the following list.

The expressions resolve to the value of the business key of the member that you want to be the current member.

Year

extract( year, localtimestamp)

Half-Year

if(extract(month, localtimestamp) < 7) then
	(1)
else
	(2)

Quarter

'Q' || cast( 
       if (extract(month, localtimestamp) <= 3) then (1)
       else ( if (extract(month, localtimestamp) <= 6) then (2)
       else ( if (extract(month, localtimestamp) <= 9) then (3)
       else (4) ) ) , varchar(1))

The curent_timestamp function returns Greenwich Mean Time while the localtimestamp function returns local time.

Month

extract(month, localtimestamp)

Week of Year

cast(extract(year, localtimestamp), varchar(4)) 
|| 'W' || cast(_week_of_year(localtimestamp), varchar(2))

Day of Year

cast(extract(year, localtimestamp), varchar(4))
|| 'W' || cast(_week_of_year(localtimestamp), varchar(2))

Day of Week

_day_of_week(localtimestamp, 7)

Day of Month

_days_between(localtimestamp, _first_of_month(localtimestamp)) + 1

Hour

extract(hour, localetimestamp)

Week of Month

if( (_days_between( localtimestamp , _first_of_month
(localtimestamp)) + 1) > 
day_of_week(_first_of_month(localtimestamp), 7) )
then (1)
else (0)
+
if (((_days_between( localtimestamp , _first_of_month
(localtimestamp))  + 1)
day_of_week(_first_of_month(localtimestamp), 7)) > 21)
then (4)
else(if (((_days_between( localtimestamp , _first_of_month
(localtimestamp)) + 1) 
 - _day_of_week(_first_of_month(localtimestamp), 7)) > 14) 
then (3)
else (if (((_days_between( localtimestamp , _first_of_month
(localtimestamp])) + 1)
 - _day_of_week(_first_of_month(localtimestamp), 7)) > 7)
then (2)
else (1)))