Skip to Content
avatar image
Former Member

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

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    avatar image
    Former Member
    Sep 27, 2012 at 07:49 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded