Year-on-year Comparison in the Multilevel Tables
Excel
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.
To achieve the target, first you need to sort the data:
Categorize and summarize data by the state name, and delete the total row:
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:
Collapse rows to the state level, and then sort data by the population growth rate. The result is shown below:
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
Firstly, filter the data:
Sort the data descendingly by Year. The result is shown below:
Group data by state, and select A2 to perform the grouping operation with Sort before group checked. The result is shown below:
In the grouping rows, calculate the population growth rates:
Sort the groups directly. Select cell C2 in the grouping row and sort them descendingly. The result is shown below:
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.