Time Period Growth Comparisons in Ad Hoc Views

Purpose

Business Intelligence is fundamentally a means of answering questions. One very common question in BI and analytics is, "How did this month's results (such as sales, profit, customer count, or visitor count) compare to last month? Or this month last year? This article explains how to answer such questions using the Calculated Fields available since Jaspersoft 5.6.

Data Considerations

In general, when looking comparing time period growth, we will three things:

1. A measure (for example, sales amount).
2. The benchmark time period (for example, previous month).
3. The measured time period (for example, current month).

Once we know this information, we can create a formula like:

This article assumes that your time period is stored in a proper database Date field. If instead your time period is stored as a String, it can be converted to a numeric value, but that is outside the scope of this article.

Let's suppose your data looks something like:

If we're interested in the month-over-month growth of Sales, we need these calculations:

1. Sum of Sales for June (324+254 = 578)
2. Sum of Sales for July (245+235 = 480)
3. % Growth (480-578)/578 = -17%

From this we conclude that sales were down 17% from June to July.

Example in Ad Hoc

Using Calculated Fields in Ad Hoc, you can easily do the simple calculations. However, the above calculations rely on specific, named months (June and July). It would be much more useful to compare the two periods without having to specify them explicitly. That way, the calculation can be used to compare any two consecutive months.  We can use an IF statement to test for the time period. Expressed in English, it might read: If the month is equal to July then add it to Current Month and if the month is June then add it to Previous Month.

The following handy Date operators can help you create formulas. For example:

Another very useful one is Today(). It can be used to determine the period that contains today; for example:

Year(Today())

The following table shows how to calculate our percent growth example by calculating the periods to compare:

• Important Note: this dataset only contains one years' sales figures so it is not necesary to test for year, if your dataset contains multiple years this example would effectively compare all of July sales with all of June sales for all years (not just the current one). Test for year with a calculation like this one: IF(MonthNumber("Date") == MonthNumber(Today())-1 AND Year("Date") == Year(Today()), Sales, 0)

With these new measures defined, you can slice the growth by other dimensions; for example, the following figure shows Monthly Percent Growth by Store:

Another example shows Year-over-Year:

2006 Distance:

IF(Year("Flight Date") == 2006, "Distance (in miles)", 0)

2007 Distance:

IF(Year("Flight Date") == 2007, "Distance (in miles)", 0)

% Growth Distance:

(Sum("2007 Distance") - Sum("2006 Distance")) / Sum("2006 Distance")

See Also

• Calculated Fields and Measures in the JasperReports Server User Guide