Skip to Content

Hi fellers,

In this blog entry I’m going to share with you some examples of Time filters that can be created in IDT and Universe Designer (BO4.1SP7).

Probably you have faced most of them but it’s good to have a place to see all of them together. I’m starting the blog with some of them but I will continue adding them.

thanks

First Day of Current Year (MSSQL Server 2012):

DIM_TIME.TIME_ID>=  dateadd(yy,datediff(yy,0,getdate()),0)

Last Day of Current Year (MSQL Server 2012):

DIM_TIME.TIME_ID<=dateadd(yy, datediff(yy,0,getdate()) +1, -1)

From Year (with current default value). This filter works with a LOV that needs to be created before creating this filter:

LOV definition:

YEAR:

SELECT DISTINCT A.ANIO FROM(

SELECT

DISTINCT  CAST(DIM_TIEMPO.ANIO AS VARCHAR(4)) AS ANIO

FROM

  DIM_TIEMPO

  UNION ALL

  SELECT ‘AÑO ACTUAL’ AS ANIO

  ) A

ORDER BY

  1   DESC

From Year

YEAR(DIM_TIME.TIME_ID) >=

CASE WHEN

@Prompt(‘1. Enter From Year’,’A’,’YEAR’:’YEAR’,Mono,Free,Not_Persistent,{‘CURRENT’})=’CURRENT’

THEN Year(getdate())

ELSE CAST(@Prompt(‘1. Enter From Year’,’A’,’YEAR’:’YEAR’,Mono,Free,Not_Persistent,{‘CURRENT’}) AS INT)

END

To Year

YEAR(DIM_TIME.TIME_ID) <=

CASE WHEN

@Prompt(‘1. Enter To Year’,’A’,’YEAR’:’YEAR’,Mono,Free,Not_Persistent,{‘CURRENT’})=’CURRENT’

THEN Year(getdate())

ELSE CAST(@Prompt(‘1. Enter To Year’,’A’,’YEAR’:’YEAR’,Mono,Free,Not_Persistent,{‘CURRENT’}) AS INT)

END

From Year – 1 to Current Year

DIM_TIME.Year >= CAST(@Prompt(‘1. Enter From Year’,’N’,,Mono,Free,Not_Persistent) AS INT) -1

and DIM_TIME.Year <= CAST(@Prompt(‘1. Enter To Year’,’N’,,Mono,Free,Not_Persistent) AS INT)

From June Previous Year to Current Year:

DIM_TIME.TIME_ID>= RIGHT(’00’+(CASE WHEN

@Prompt(‘1. Enter From Year’,’A’,’YEAR’:’YEAR’,Mono,Free,Not_Persistent,{‘CURRENT’})=’CURRENT’

THEN Year(getdate())

ELSE CAST(@Prompt(‘1. Enter From Year’,’A’,’YEAR’:’YEAR’,Mono,Free,Not_Persistent,{‘CURRENT’}) AS INT)

END )-1,4)+’0701′

Today:

@Select(Time\Time Id) = convert(date,getdate())

Last 24 Months (Periods where definition is YYYY-MM)

DIM_TIME.PERIOD Between (@Prompt(Period)-200) And @Prompt(Period)

Last Fact Date ( This filter works with a LOV)


LOV Definition:


FACT DATE

SELECT DISTINCT

A.FECHA FROM(

                                SELECT DISTINCT

                                    convert(varchar,convert(date,FACT_TABLE.TIME_ID)) AS DATE

                                FROM

                                      FACT_TABLE

                                 UNION ALL

                                  SELECT ‘LAST FACT DATE’ AS DATE

                              ) A

ORDER BY

  1   DESC

Last Fact Date

convert(varchar,convert(date,FACT_TABLE.TIME_ID)) =

CASE WHEN

@Prompt(‘Enter Date: ‘,’A’,’Fact Date’:’DATE’,Mono,Free,Not_Persistent,{‘LAST FACT DATE’})=’LAST FACT DATE’

THEN convert(varchar,convert(date,(select Max(FACT_TABLE.TIME_ID) From FACT_TABLE)))

ELSE @Prompt(‘Enter Date: ‘,’A’,’Fact Date’:’DATE’,Mono,Free,Not_Persistent,{‘LAST FACT DATE’})

END

Previous 12 Months (Period Basic YYYY-MM works with PERIOD LOV)


PERIOD LOV

SELECT DISTINCT

A.PERIOD FROM(

                                SELECT DISTINCT

                                CAST(T.PERIOD AS VARCHAR(6)) AS PERIOD

                                FROM

                                      FACT_TABLE V,

                                      DIM_TIME T

                                  WHEREV.TIME_ID=T.TIME_ID

                                 UNION ALL

                                  SELECT ‘LAST PERIOD’ AS PERIOD

                              ) A

ORDER BY

  1   DESC


Previous 12 Months

DIM_TIME.period >=

CASE WHEN

@Prompt(‘Enter Period: ‘,’A’,’Period’:’PERIOD’,Mono,Free,Not_Persistent,{‘LAST PERIOD’})=’LAST PERIOD’

THEN (select max(t.period)-100 From FACT_TABLE v, DIM_TIME t WHERE v.TIEMPO_ID=t.TIEMPO_ID )

ELSE @Prompt(‘Enter Period: ‘,’A’,’Period’:’PERIOD’,Mono,Free,Not_Persistent,{‘LAST PERIOD’})-100

END

and DIM_TIME.period <

CASE WHEN

@Prompt(‘Enter Period: ‘,’A’,’Period’:’PERIOD’,Mono,Free,Not_Persistent,{‘LAST PERIOD’})=’LAST PERIOD’

THEN (select max(t.period) From FACT_TABLE v, DIM_TIME t WHERE v.TIEMPO_ID=t.TIME_ID )

ELSE @Prompt(‘Enter Period: ‘,’A’,’Period’:’PERIOD’,Mono,Free,Not_Persistent,{‘LAST PERIOD’})

END

Current Period


DIM_TIME.period =

CASE WHEN

@Prompt(‘Enter Period: ‘,’A’,’Period’:’PERIOD’,Mono,Free,Not_Persistent,{‘LAST PERIOD’})=’LAST PERIOD’

THEN (select max(t.period) From FACT_TABLE v, DIM_TIME t WHERE v.TIEMPO_ID=t.TIEMPO_ID )

ELSE @Prompt(‘Enter Period: ‘,’A’,’Period’:’PERIOD’,Mono,Free,Not_Persistent,{‘LAST PERIOD’})

END

Previous Month (Period basis)


DIM_TIME.period =

CASE WHEN

@Prompt(‘Enter Period: ‘,’A’,’Period’:’PERIOD’,Mono,Free,Not_Persistent,{‘LAST PERIOD’})=’LAST PERIOD’

THEN (select max(case when right(t.period+”,2)=’01’ then t.period-89 else t.period-1 end) From DIM_TIME t WHERE (t.month)=MONTH(getdate()) and (t.YEAR)=year(getdate()))

ELSE @Prompt(‘Enter Period: ‘,’A’,’Period’:’PERIOD’,Mono,Free,Not_Persistent,{‘LAST PERIOD’})

END

Current Year (Period basis)


DIM_TIME.period >=

CASE WHEN

@Prompt(‘Enter From Period: ‘,’A’,’Period’:’PERIOD’,Mono,Free,Not_Persistent,{‘LAST PERIOD’})=’LAST PERIOD’

THEN (select max(t.period) From FACT_TABLE v, DIM_TIME t WHERE  v.TIME_ID=t.TIME_ID and t.month=1 )

ELSE @Prompt(‘Enter From Period: ‘,’A’,’Period’:’PERIOD’,Mono,Free,Not_Persistent,{‘LAST PERIOD’})

END

and DIM_TIME.period <=

CASE WHEN

@Prompt(‘Enter To Period: ‘,’A’,’Period’:’PERIOD’,Mono,Free,Not_Persistent,{‘LAST PERIOD’})=’LAST PERIOD’

THEN (select max(t.period) From FACT_TABLE v, DIM_TIME t WHERE v.TIME_ID=t.TIME_ID )

ELSE @Prompt(‘Enter To Period: ‘,’A’,’Period’:’PERIOD’,Mono,Free,Not_Persistent,{‘LAST UPDATED’})

END

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply