A Code Example of Computing Link Relative Ratio and Year-on-year Comparison with esProc

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:
Compute link relative ratio and year-on-year comparison of each month’s sales amount within the designated period. The data comes from table order. Some of the data is shown below:

 

esProc_relative_ratio_1

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:

 

esProc_relative_ratio_2

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

 

esProc_relative_ratio_3

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 month’s link relative ratio is empty (i.e. January 2011). Computed results are:

 

esProc_relative_ratio_4

 

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: 

esProc_relative_ratio_5

 

A5: 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:  

esProc_relative_ratio_6

 

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:

esProc_relative_ratio_7

Advertisements

About datathinker

a technical consultant on Database performance optimization, Database storage expansion, Off-database computation. personal blog at: datakeywrod, website: raqsoft
This entry was posted in Application, Program Language and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s