A Coding Example of Computing Link Relative Ratio and Year-on-year Basis

Link relative ratio refers to comparison between the current data and data of the previous period. The interval is usually one month. For example, divide sales amount of April by that of March, and you get the link relative ratio of April. Hour, day, week and quarter can also be used as the time interval. Year-on-year comparison is the comparison between the current data and data of the corresponding period of the previous year. For example, divide sales amount of April 2014 by that of April 2013. In business, data of multiple periods is usually computed to find the variation trend. 

Seeking link relative ratio and year-on-year comparison is common inter-row and inter-group computations, which are easy to be performed with esProc. The following example is used to illustrate the computations. 

Case description:

To compute the link relative ratio and year-on-year comparison of each months sales amount within the designated period. The data comes from table order. Some of the data is shown below:

esProc code:

A1=esProc.query("select * from sales3 where OrderDate>=? and OrderDate<=?",begin,end)

A2=A1.groups(year(OrderDate):y,month(OrderDate):m;sum(Amount):mAmount)

A3=A2.derive(mAmount/mAmount[-1]:lrr)

A4=A3.sort(m)

A5=A4.derive(if(m==m[-1],mAmount/mAmount[-1],null):yoy)

Code interpretation:

A1: Query in the database according to periods. begin and end are external parameters. Such as, begin="2011-01-01 00:00:00"end="2014-07-08 00:00:00"(i.e. the date of today which can be obtained through now() function). Some of the query results are as follows: 

A2: Group orders by year and month, then summarize and seek each months sales amount. Some of the computed results are as follows: 

A3: Add a new field Irr, i.e, the link relative ratio on a month-on-month basis. The code is mAmount/mAmount[-1], in which mAmount represents sales amount of the current month, and mAmount[-1] represents that of the previous month. Note that the initial months link relative ratio is empty (i.e. January 2011). Computed results are: 

A4: Sort A3 by month and year to compute year-on-year comparison. Complete code should be: =A3.sort(m,y). Since A3 is originally sorted by the year, so we just need to sort by the month, the code is: A3.sort(m), which has a higher performance. Some of the computed results are:  

A5: Add a new field yoy, i.e., theyear-on-year comparison of monthly sales amount. The code is: if(m==m[-1],mAmount/mAmount[-1],null), meaning that the computation of year-on-year comparison is only performed over the corresponding months. Note that the year-on-year comparison for months of the initial year (i.e. the year 2011) is always. Some of the computed results are:  

A row of code, A6=A5.sort(y:-1,m), can be added to make observation easier. That is, sort A5 in descending year order and ascending month order. Note that the data comes to an end in July 2014. Results are shown below: 

Views: 555

Comment by Jim King on July 30, 2014 at 8:39pm

Compare the sales amount with the previous month, compare with the corresponding period of last year...in business analytics, such data comparison is quite necessary to find the business trends,how do you calculate them? And what do you think of the method illustrated above?

Comment

You need to be a member of Oracle Community to add comments!

Join Oracle Community

Oracle Community On

I'm not a fan of advertising, and so I will not be including any advertisements on OracleCommunity.net. However, managing this community does not come without cost! If you are willing to donate to help pay for the monthly community fees and domain services I accept Bitcoin and PayPal donations.

Donate Bitcoins

Badge

Loading…

Blog Posts

Start a great career in Data Integration with Oracle Training and Certification Courses

Organizations today rely on data integration technologies in order to gain a unified view of data coming from disparate sources for informed and timely decision making. These technologies bring together data from multiple sources and transform them into meaningful and valuable information. Oracle is one of the leading providers of data integration technologies that are widely accepted by organizations all over the world. Oracle training and certification courses in the field of data…

Continue

Posted by Michael Warne on January 25, 2016 at 1:57am

Roll Forward Physical standby database in 12c

As you are aware that rolling forward a physical standby via an incremental SCN backup method is the simplest method of getting your standby database with lag into sync with the primary database. The lag can be due to missing archives on the primary which haven’t been shipped or applied on the standby.

 

With 12c, the roll forward technique is a bit different. In the prior versions, we had to initially take an incremental backup of the primary database from the SCN where the…

Continue

Posted by Shivananda Rao P on January 11, 2016 at 11:37pm

Learn IBM Sterling B2B Integrator Online Training at VirtualNuggets

VirtualNuggets is Offering Instructor Led Live IBM Sterling B2B Integrator Online Training. we are the Industry leader in providing IBM Sterling B2B Integrator Tools Training Services, Offering Corporate Trainings Both In Online and In-House and Online Training for Individuals. VirtualNuggets is Offering Online Training for all IT technologies across the globe. Our IBM Sterling B2B Integrator online training methodology focused on hands on experience of Sterling Integrator. We also arrange…

Continue

Posted by Vasu Buddi on October 9, 2015 at 11:25am

IBM Integration Bus Development Online Training

VirtualNuggets is Offering Instructor Led Live IBM Integration Bus Development. VirtualNuggets is the Industry leader in providing Integration Bus Tools Training Services, Offering Corporate Trainings Both In Online and In-House and Online Training for Individuals. VirtualNuggets is Offering Online Training for all IT technologies across the globe. Our IBM Integration Bus Development online training methodology focused on hands on experience of IBM Integration Bus Development. We also…

Continue

Posted by Vasu Buddi on October 6, 2015 at 4:13am

© 2016   Created by Steve Karam.   Powered by

Badges  |  Report an Issue  |  Terms of Service