Year-on-year Comparison in the Multilevel Tables

Find the top three states in terms of population growth rate in 2010 on the basis of the given population data.

Excel

esCalc_excel_year_on_year_1
To calculate the population growth rates in 2010, you only need the population census data of the years 2010 and 2000. So the first thing is to filter the data, and then copy the viewable data after the filtering to a new worksheet for further summarization and analysis.
esCalc_excel_year_on_year_2
To achieve the target, first you need to sort the data:
esCalc_excel_year_on_year_3
Categorize and summarize data by the state name, and delete the total row:
esCalc_excel_year_on_year_4
Then, in the following steps, calculate the population growth rate of each state in the grouping rows. As this isn’t a standard aggregation operation, you must compose a user-defined formula:
esCalc_excel_year_on_year_5
Collapse rows to the state level, and then sort data by the population growth rate. The result is shown below:
esCalc_excel_year_on_year_6
As you can see, the states whose population growth rates are among the top 3 are Nevada, Arizona, and Utah. If need to use the data from these 3 states, you will have to copy it to a new worksheet manually.

In Excel, to perform a year-on-year comparison on a multilevel table, you have to paste formulas manually. That could be a large amount of work when the table contains huge data and many groups. If the aggregation is based on a part of the data, you need to rearrange it in a new worksheet manually, resulting in low efficiency and a greater risk of error occurring.

esCalc

esCalc_excel_year_on_year_7
Firstly, filter the data:
esCalc_excel_year_on_year_8
Sort the data descendingly by Year. The result is shown below:
esCalc_excel_year_on_year_9
Group data by state, and select A2 to perform the grouping operation with Sort before group checked. The result is shown below:
esCalc_excel_year_on_year_10
In the grouping rows, calculate the population growth rates:
esCalc_excel_year_on_year_11
Sort the groups directly. Select cell C2 in the grouping row and sort them descendingly. The result is shown below:
esCalc_excel_year_on_year_12
If you only need the data of the top three states, then filter the groups again.

With esCalc, the desktop BI software, you can just enter one formula for accomplishing the whole year-on-year comparison; you can delete data for a filtering operation without manual work; and you can perform a sort directly on grouping rows and change the position of each group as a whole. The esCalc operation mode makes it very convenient to handle the kind of problem just mentioned, regardless of the amount of data or the number of groups.