HOW TO CREATE ROLLING MONTHS LOGIC IN IDT

[Former Member]

Hi All,

I have universe created on top of multiprovider using information designer tool (IDT) with relational connection. I need to create rolling months predefined filters in the universe, Like  rolling 3 months,rolling 13 months etc. can anyone suggest or guide in deriving the logic for this in IDT

Thanks

Usha

[Former Member]

Two things:

Firstly, you need to be clear what you mean by rolling three months. Is it:

The month we are currently in plus the previous 2

The month we are currently in plus the previous 3

The previous 3 months before the one we are currently in.

The second thing - what DBMS are you using as if you are doing it at database level within IDT, you will need to use the correct syntax e.g. Oracle uses sysdate, SQL Server uses getdate

[Former Member]

Hi Mark,

The universe is a multi sourced universe that is the data sources are SAP BI DSOs and  SAP BI Cubes

Rolling3 months means.. suppose we are in the month of feb 2013. i need rolling for Nov2012,Dec 2012 and Jan 2013

Please let me know if need any further information.

Thanks

Usha

[former_member4998]

Hi...Try this.

select

DATE_ID as DATE_ID,

'Last 3 month' as Periods

from td_Date

where

CalendarDate between DATEADD(mm,-3,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))

and DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)))

union all