A link-relative
ratio is a percentage got by comparing the data of current period and that of
the previous period. Usually the interval between the two periods is a month (sometimes
an hour, a day, a week and a quarter). The link-relative ratio of April is computed
by dividing, for example, its sales amount by that of March. A year-on-year
comparison is a comparison between the current period and the same one from the
previous year. For example, the comparison between sales amount of April 2014 and
that of April 2013. In practical business, data of multiple periods is usually compared
in order to find the variation trend.

The
inter-row and inter-group computations related to link-relative ratio and
year-on-year comparison are common and can be easily handled with esProc. The
following example will show you how esProc works.

**Case description:**

Compute
link-relative ratio and year-on-year comparison of growth rate of each month’s
sales amount in a specified period. Below is a selection from the

*Orders*table:**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 explanation:**

*begin*and

*end*are external parameters. Such as,

*begin="2011-01-01"*,

*end="2014-07-08"*(i.e. the data of the current day which can be obtained through

*now()*function). Below is a selection of the query result:

A2: Group orders by the year and the month, and summarize data to get each month’s 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 expression 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 value of the first month’s (i.e. January 2011) link-relative ratio is null. Computed results are:

A4: Sort A3 by the month and the year to compute the growth rate of year-on-year comparison. Complete code should be:

*=A3.sort(m,y)*. Since A3 has been sorted by the year, so we just need to sort it by the month, that is,

*A3.sort(m)*. This has a higher performance. Some of the computed results are:

A5: Add a new field

*yoy*, i.e., the year-on-year comparison of monthly sales amount. The expression is

*if(m==m[-1],mAmount/mAmount[-1],null)*, which means that the comparison can only be made between the same months. Note that the values of year-on-year comparison of months in the initial year (i.e. the year 2011) are always nulls. Some of the computed results are:

For the convenience of observation, one more line of code,

*A6=A5.sort(y:-1,m)*, can be added. That is, sort A5 by the year in descending order and by the month in ascending order. Note that the data ends in July 2014. Results are as follows:

## No comments:

## Post a Comment