Rolling time series in a relational universe (based on SQL source)

[Former Member]

Hello,

I have created a relational universe (unx) based on a SQL 2008 table that has data as seen below...

Year     Month     Quarter     Revenue
2011      Jan           Q1                500
2011      Feb           Q1                500
2011      Mar           Q1                500
2011      Apr           Q2                666
2011      May          Q2                666
2011      Jun           Q2                666
2011      Jul            Q3                333
2011      Aug          Q3                333
2011      Sep          Q3                333
2011      Oct           Q4                888
2011      Nov           Q4                888
2011      Dec           Q4                888
2012      Jan            Q1                323
2012      Feb           Q1                323
2012      Mar           Q1                323
2012      Apr           Q2                668
2012      May          Q2                668
2012      Jun           Q2                668
2012      Jul            Q3                123
2012      Aug           Q3                123
2012      Sep           Q3                123

I have a requirement to display rolling 4 quarters based on the user input Month and Year..

So if user enters Month "Aug" and Year "2012", then result should display last 4 quarters (Q2/2011, Q1/2011, Q4/2012, Q3/2012)...Since input month "Aug" falls in Q3, get previous quarter and prior quarters i.e Q2 and prior (logic Q3 - 1, Q3 - 2 etc)

Result...

2011      Q3      333
2011      Q4      888
2012      Q1      323
2012      Q2      668

I would like to add this logic in the information design tool (via business layer) so that this logic can be re-used in different WebI reports or Crystal reports..

Any help or guidance will be appreciated on how to acheive this in the information design tool.

Thank you
Ann

[former_member207342 · Accepted solution]

Hi

Here is the solution i could think of

Create a derive table in Universe with a prompt where user will select the month and year

Say Sep,2012

Now append day 01- and make it look like a date and pass that date to following query as parameter.

Sample Query :

    select  datepart(qq,dateadd(qq,-4,'2012-09-27')) qtr, datepart(yy,dateadd(qq,-4,'2012-09-27'))

    UNION ALL

    select datepart(qq,dateadd(qq,-3,'2012-09-27')) qtr, datepart(yy,dateadd(qq,-3,'2012-09-27'))

    UNION ALL

    select datepart(qq,dateadd(qq,-2,'2012-09-27')) qtr, datepart(yy,dateadd(qq,-2,'2012-09-27'))

    UNION ALL

    select datepart(qq,dateadd(qq,-1,'2012-09-27')) qtr, datepart(yy,dateadd(qq,-1,'2012-09-27'))

    Derivae table:

    select  datepart(qq,dateadd(qq,-4,'<DATEPARAM>')) qtr, datepart(yy,dateadd(qq,-4,'<DATEPARAM>'))

    UNION ALL

    select datepart(qq,dateadd(qq,-3,'<DATEPARAM>')) qtr, datepart(yy,dateadd(qq,-3,'<DATEPARAM>'))

    UNION ALL

    select datepart(qq,dateadd(qq,-2,'<DATEPARAM>')) qtr, datepart(yy,dateadd(qq,-2,'<DATEPARAM>'))

    UNION ALL

    select datepart(qq,dateadd(qq,-1,'<DATEPARAM>')) qtr, datepart(yy,dateadd(qq,-1,'<DATEPARAM>'))

And then you can query this derive table and pass its values to your main query as a filter. you might need to use subquery over here.