Visualized Data Computing in Business Spreadsheet Software

SQL is a database query and programming language for retrieving, updating, and managing the data from relational database.SQL was certified to meet ANSI in 1986, and became an international standard in 1987. Nowadays, SQL becomes a basic requirement for every programmer. However, the advantages cannot obscure the disadvantage. SQL is especially designed for technical personnel. SQL syntax is highly abstract, the logic is hard to understand, and only those with strong technical background can grasp it. However, in the business office workspace, the non-technical users will usually need the query and process data by themselves. They hope there is a reporting tool with a technical requirement as low as almost zero, and a computing capability as strong as SQL tool. esCalc is such a tool.

For example, a secretary needs to prepare a latest list of big clients for a regularly scheduled meeting. The big clients are those who accounts for 50% of the total sales for the company the secretary is working with. Assign or request an IT team to handle it will cost a great deal of time to coordinate. Therefore, he decides to calculate all by himself. He can use the business language to describe the calculation process:

Filter by time: Filter out the data of the half year in the order table.
Group and summarize: Group the data by client, and the total sales of orders in the group will be the sales of clients.
Set the standard for comparison by calculating: The total sales value is the sales sum of all clients. Multiply the total sales by 0.5, and the result is the standard of comparison.
Sort by sales value: Sort the clients by sales descendingly.
Calculate the cumulative value: Sum up the sales one by one from the highest to the lowest. Suppose if there is a client in the 3rd place, then the cumulative value of this client is the sum of the three sales values among the top 3.
Filter out the big client: The calculation goal is to find the clients whose cumulative value is less than the standard of comparison. Suppose if the standard of comparison is greater than the cumulative value of the 5th clients but less than that of the 6th, then the top 5 clients can all be regarded as the big clients.

Previously, those business users who are not familiar with SQL cannot implement the above calculation. Nowadays, esCalc, the business spreadsheet software enables them to operate visually and calculate intuitively in their own business language and thinking pattern. For another example:

Find the clients whose annual sales are among the top 10 in every year.
Collect statistics on the newly opened retail stores, including: How many retail stores newly opened this year? How many retail stores have profited over one million dollars? Of these retail stores, how many of them have opened their businesses overseas?
How much does the sales increase compared with that of the previous month?

The above calculation problems are quite common in the modern business office workspace. In fact, these problems are just the combination of SQL filtering, grouping, summarizing, distinct, horizontal joining / vertical union, and other calculation methods. esCalc is the SQL without any requirement on the technical background of users. esCalc embodies and visualize the calculation methods innovatively, enabling the business personnel to be same competent and capable as the IT technicians in some business computing.

In the following sections, we will introduce the way to implement these calculation methods with esCalc.

Filter
Filtering is to screen out the data with specific criteria. For example, in the above case, it is the filtering action by date. To put specifically, it is to filter the data greater than July 1 of 2010 and less than the December 31 of 2010. Firstly, open the order table to retrieve the data:
esCalc spreadsheet filter 1

On the column OrderDate, right click on any cell except for the header, and the operation menu will appear, as shown in the below figure:
esCalc spreadsheet filter 2

Click Filter and the Setting interface will appear. Following the setting given in the below figure, the data greater than July 1 of 2010 can be filtered out:
esCalc spreadsheet filter 3

Once the procedure is completed, the result of filtering can be viewed intuitively, as shown in the below figure:
esCalc spreadsheet filter 4

On the existing results, the further filtering can be implemented. For example, in this case, the data, which is less than the December 31 of 2010, is to be filtered out. Repeat the above-mentioned procedure in which the involved setting interface is as follows:
esCalc spreadsheet filter 5

The above data filtering procedure equals to that of SQL.

Besides, another thing to notice is that esCalc filters twice in 2 steps respectively, that is, firstly filter the data greater than July 1, 2010, then filter the data less than December 31, 2010. In fact, the two steps are allowed to be incorporated into 1 step. For example, compose the below expression in the “Expression”:
esCalc spreadsheet filter 6

For esCalc, in order to reduce the difficulty, we suggest those who are familiar with Excel formulas taking the 1st step to filter, and those who are unfamiliar with the relevant formulas taking the 2nd step to filter.

The step-by-step calculation is an important feature of esCalc that can converts the complex calculation goal into the several simple objects. Since every step is a simple action, the error probability is reduced, and it is easy to remedy even if any error occurs. By comparison, SQL requires all calculations to complete in 1 step, which is not only complex and difficult to understand, but also error-prone. In addition, it is also quite difficult and time-consuming to detect errors in the complex statements. With the calculation being carried on, the comparison in this respect will become ever more evident.

Group and Summarize
Grouping is to allocate the data to several groups according to a specific rule; Summarizing is an action to perform aggregate calculation on a group of data. For example, in this case, group the data by clients, and summarize the sales for each client.

On the basis of the calculation above, in the column Client, right click on any cell and select the Group on the menu, as shown in the below figure:
esCalc spreadsheet filter 7

Then, select Quick operation menu option. To make it easy to understand in this demonstration, we have simplified the standard operation procedure, that is, adopt the default parameters and operate directly. The result of grouping is shown below:
esCalc spreadsheet filter 8

Then, calculate the cumulative value for each group of data. Simply input the formula in any cell of the summary row of 2, 6, 8, and other rows. For example, in E2, enter the formula ={E3}.sum(), as shown in the below figure:
esCalc spreadsheet filter 9

In esCalc, users only need to input the formula once and the formula will be auto-pasted to the related cells (homocells by name) and auto-adapted. Therefore, after entering the formula in E2, the cumulative value will be calculated in E6, E8, and other cells respectively and automatically, of which the formula in E6 is:={E7}.sum().

Grouping is equivalent to the group by clause in SQL, and summarizing is equivalent to the select sum(…) clause. One thing to note is that the grouping and summarizing in esCalc can be carried out separately. For example, you may need to implement grouping only to view the data, and summarizing is not necessary; By comparison, SQL will have the grouping and summarizing done at the same time. Otherwise, error will be reported.

Then, proceed to calculate the standard of comparison – 50% of total sales. Firstly, insert the blank row, then click the level “1” on the left to make the row displaying area collapse for easy monitoring, as shown in the below figure:
esCalc spreadsheet filter 10

After collapsing, only the data of the sales of each client is displayed. In the collapsed display area, the level “1” indicates the level of Client after grouping. Then, calculate the standard of comparison. In E2, enter the formula:={E3}.sum()*0.5, as shown in the below figure.
esCalc spreadsheet filter 11

The result of this step is the standard of comparison that will be taken as the filter condition for future use. The intermediate calculation process equals to the grouping, subtotal in the group, and of SQL.

Sort
Sorting is an action to adjust the orders according to a specific sequence. For example, sort the number from the greatest to the smallest or sort the characters alphabetically from a to z. In this case, the clients will be sorted descendingly by the sales value. Simply select E3 or any homocell of E3, then select “Desc” in “Quick operation”, as shown in the blow figure:
esCalc spreadsheet sort 1

  
Without any parameter settings required, the result will appear immediately, as shown in the below figure:
esCalc spreadsheet sort 2

The above sorting procedure is equivalent to the Order by clause of SQL. Needless to say, the corresponding writing style of SQL is much more complex.

Cumulative value
Cumulative value calculation is a kind of inter-row calculation, for example, the cumulative value of E6 equals to E3+E6, and the cumulative value of E10 equals to E10+E6+E3. To put it intuitively, Current value + Cumulative value of the previous row. In this case, the next step is to calculate the cumulative value of the sales. Append a column of Addup, and input the expression to F6:F3+E6, as shown in the below figure:
esCalc spreadsheet cumulative value 1

As can be seen, esCalc is intuitive to calculate the cumulative value, as simple as “current value+cumulative value of the previous row”. Similarly, the year-on-year comparison and the link relative ratio calculation are various kinds of inter-row calculation. It is quite easy to implement these inter-row calculations through esCalc.

As a complete case, the ultimate step is to compare the cumulative value with the standard of comparison by filtering, as shown in the below figure:
esCalc spreadsheet 1
esCalc spreadsheet 2
The QU, NR, ERN, GCD, and RQ is the big clients we are looking for. The calculation in this step is equivalent to the SQL below:
with grouped as(
select top 1000 Client,sum(Amount) as Amount from(
SELECT Client
,OrderID
,OrderDate
,SalesMan
,Amount
FROM salesSQL
where OrderDate>=’07/01/2010′ and OrderDate<='12/31/2010' )T1 group by Client order by Amount desc ), standardtable as( select sum(Amount)*0.5 as standard from grouped ) select * from ( select Client,Amount,( select sum(Amount) from grouped as D1 where D1.Amount>=D0.Amount
)as addup
from grouped as D0
)T1 where addup<=( select standard from standardtable   ) Obviously, business users cannot grasp the above complex SQL; esCalc has no technical requirements on users to perform the equivalent calculations. join, union, and others
The join is to merge two tables horizontally, and union is to merge vertically. Both join and union actions are typically used to merge the data together. For example, in the above case, the data in the column Client for the big clients are all of the abbreviations that is inconvenient for reading. We can bring in and join the full names from client table.

Firstly, use esCalc to open the client table, as shown in the below figure:
esCalc spreadsheet 3

Judging from the above-mentioned data, we can find that there are similar data in the column ID from the client table and the Client column from the big client table. We mark these two columns as “Master cell”, select Copy action or press Ctrl+C, and then select “join” for the big clients, as shown in the below figure:
esCalc spreadsheet 4

To this point, the data is merged, as shown in the below figure:
esCalc spreadsheet 5

The above are designated as “left join” clause in SQL. In addition, for the SQL full join, union, union all, distinct, and other clauses, we can all find the corresponding actions in esCalc. In facts, the esCalc can be regarded as the graphic SQL. Their basic functions are in the one-to-one correspondence relations.
   
All in all, esCalc implements the visualized SQL mechanism. The complex and lengthy SQL statements become so simple and intuitive that even the business personnel without any technical background can use it to handle the complex data calculation.