Getting Started with Adobe After Effects - Part 6: Motion Blur

Upload Image Close it
Select File

I will write a series of blogs on data analytics, data statistics, spreadsheet computing, scripts computing and related tools. I am a BI technology consultant for Raqsoft with 10 + years of experience on BI/OLAP application, statistical computing and analytics. Email: Website: Blog:
Browse by Tags · View All
SQL 13
esProc 10
database 9
Java development tool 9
Data Analytics 8
structured data computing 7
data processing 6
hadoop 6
data analysis 6
data computing 6

Archive · View All
April 2014 7
July 2013 6
January 2014 4
August 2013 4
November 2012 4
December 2013 3
June 2013 3
May 2013 3
April 2013 3
March 2013 3

Datakeyword's Blog

A solution to further computations in groupded retail data

Mar 27 2013 12:00AM by Jim King   

The common analytics in retail industry include analysis for finance, market, commodities, customers, vendors, and salesman. The retail data analytics gives a summary on top performers, bottom performers, key value items, sales performance, forecasting, trend and seasonality. Retailers need to make  strategies based on the result of retail data analytics.

One of problems that most complained during the retail data analytics is further computations on grouped data. While the retail data stored in Excel, it could be time-consuming and fussy with computations on grouped data, especially when there are a lot of groups. 

The spreadsheet enables users to group the data easily. But it is not convenient for further computations on the grouped data. For example, please check the wine retail data after group and summary in Excel as below sheet:

Say, if we are to compute the total amount of each wine or even each type. In the spreadsheet, it is a bit difficult to carry on these calculations or actions based on the above: calculate the ”UnitPrice*Stock” amount; sort the data in the group by inventory level; add an additional level of grouping by Name to calculate the inventory of the same type of wine; calculate the average unit price of each type of wine; and even highlight the subtotal section such as the F4 and F10. 

To calculate the resulting amount of ”UnitPrice*Stock”, a new column G need to be appended along with an expression of “=E4*F4”, and then drag the expression to every row of this column. Because UnitPrice is unavailable in the subtotal row, the formula is invalid there and must be removed for each row manually, which is error-prone since there are many types of wines.

To sort the data in a group by the inventory level, you can only sort manually on each group of data since the data is allocated to several irrelevant groups. For example, the 1st group of data is from the row 4~9. It is error-prone if the data volume is huge. Another solution is to ungroup and present the data in a 2D sheet without subtotal, sort by Type and Stock, and then summarize in groups. In this solution, if there is any data in the subtotal row like “proportion of subtotal to total”, then users will have to recalculate these data once the row of subtotal disappears in the procedure. All in all, both solutions suffer from obvious drawbacks.

Add another level of grouping by Name to calculate the inventory of a same type of wine. Because the data is not sorted by Name, the data cannot be grouped directly and we have to go back to solve the previous problem.

To calculate the average unit price of each type of wine, both the 2 solutions available are quite cumbersome. The first solution is to ungroup and paste the data into 2 pieces, either of which will respectively go through the average unit price calculation or the inventory summary calculation, then join the summary values of these 2 pieces of data. The second solution is to write the formula in every subtotal section, for example “=SUBTOTAL(1,E4:E9)” in E3. Since the data in various groups differ, users will have to write N different formulas again and again and errors may quite easily occur.

To highlight the subtotal section, users have no choice but to repeat the simple action on each group one after another manually. Without any means to automate, it is so sarcastic when considering the Office Automation. 

In facts, the above problem can be solved with esCalc. esCalc introduces the concept of homocell to present the business relations between cells so as to enable the auto-processing on data of various business senses. For example, if inputting the formula =E4*F4 in G4 for once, then the formula will be pasted to all rows of the details data except for the summary row, as shown in the below sheet:

The data in the group is sorted by the inventory level. In the same way, just sort once in the F4, F5 or F11 cell, the respective positions will be auto-identified, and the data in each group will also be auto-sorted, as shown in the below sheet:

As you know, the esCalc homocell mechanism can solve other problems automatically, and we will not dwell on it for it’s the topic of another essay.

Tags: business intelligence, excel, analytics, retail data analytics, esCalc,spreadsheets

Jim King
97 · 2% · 582


Your Comment

Sign Up or Login to post a comment.

"A solution to further computations in groupded retail data" rated 5 out of 5 by 2 readers
A solution to further computations in groupded retail data , 5.0 out of 5 based on 2 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]