Skip to Content
0
Former Member
Sep 21, 2012 at 03:23 PM

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

19 Views

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