A Standard Method of Building Reports Presenting Link-Relative Ratios and Year-on-year Growth Rates

A link-relative ratio is a percentage got by comparing the data of current period and that of the previous period. A year-on-year growth rate is the rate of change expressed between the current period and the same one from the previous year. Different reporting tools have different ways to calculate the link relative ratios and the year-on-year growth rates. Some do the calculations in SQL, some perform the calculations using elusive controls, and others can only achieve the goals by writing complicated report scripts.

esProc supports handling inter-row calculations and the calculations involving relative positions automatically, enabling a standard method of building reports that present the link relative ratios and year-on-year growth rates. The method applies to various reporting tools. So esProc is an ideal tool for report data source preparation. The reporting tool regards an esProc script as the stored procedure, passes parameter to it and gets the returned result after execution through JDBC. Learn more from How to Use esProc to Assist Reporting Tools. 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 and present the results in a report. 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:

A1: Retrieve data from database according to the specified period. 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: