Complex Computations in Categorized Tables

According to the box office revenue data of several movies in a week, find out data of the movie whose box office revenue rises for the longest continuous days in the week.

Excel

To calculate the number of days when a movie’s box office revenue keeps rising, first you need to take movie as the primary keyword and date as the secondary keyword to sort the data. The result is shown below:

In column D, calculate the consecutive days of increase in box office revenue per movie per date, such as Marvel’s The Avengers. Enter a formula in D3 and copy it to other cells in the same column using drag-and-drop. Manual editing is needed to perform the copy & paste. So there will be a great deal of work when the amount of data is large.

Group and aggregate records by movie name to find out the maximum aggregate value in column D, and remove the summary rows. The result is shown below:

Collapse rows to the group level and perform sort by the longest consecutive days of box office revenue rising. The result is shown below:

Now you can view the movie with the longest days of consecutive box office revenue increase in the worksheet. To keep only the data of this movie, copy and paste the data to a new worksheet manually. If the work is done through the filter operation, then you must copy the grouping rows to another worksheet to filter. Otherwise, it will be hard to distinguish the detailed data rows and the grouping rows.

In Excel, special attention should be paid to the order when calculating. Finish calculating the desired data before data grouping. If you do calculation in every group, paste the formulas one by one manually to each category. The operation will become cumbersome when there are many categories. Besides, you are unable to specify levels for the filtered data, so data you are going to work on should first be organized, making the whole processing complicated.

esCalc

Group by the movie name with Sort before group checked:

Calculate the daily number of continuous rising days of each movie:

In the grouping rows, calculate the longest consecutive days in which the box office revenue keeps rising. To do so, enter the formula ={D3}.max() in D2. The result is shown below:

With esCalc, the desktop BI software, you can directly filter the groups:

In esCalc, you only need to follow the natural train of thought to operate stepwise to complete the complex calculation. Because only one formula suffices to do the same calculation in all homocells, the copy and paste operation is not needed when handling the batch calculation. Data filtering will be performed within homorows, so that the inter-influence between various kinds of data in a same column can be avoided.