The basic function of a calculator is computing,
which can be as simple as the four arithmetic operations and also as complex as
the calculation for the next move in chess with Deep Blue. Among
these, esCalc is a desktop data calculation tool for the business users to
handle the occasional, complex, and business-related data computation. Such
tools are named as the desktop BI software.
For example, a stock analyst is required to
recommend some stocks to the clients urgently. Among all calculations involved,
the analyst needs to find shares from more than 20 daily trading stocks which
had risen on 5 consecutive days in previous month. He opens this desktop BI software and imports the daily data of the more than 20 stocks. He continuously
monitors and analyzes the data, works out the outline of algorithms, and then groups,
summarizes, sorts, filters and takes other possible operations to calculate the
results with some simple formulas. At last, he gets the result and makes the
recommendation successfully.
The similar calculations also include:
- For all clients of the insurance company, what’s the average insurance price of those who bought the basic insurance first and then bought the additional insurance?
- In the 3 months with the most client complaints, find out the top 3 products with the highest defective rate.
- For the top N sales persons who achieved the 50% of the total sales for the company, what are their respective sales proportions to that of their respective sales team?
esCalc is the plug-and-use desktop BI
software with the powerful computational capability for business users to grasp
easily, owing to the following advantages:
Typical Desktop
Application
The installer of esCalc is only dozens of
MB. The installation procedure only requires a few clicks and can be run immediately
after installation. As a JAVA application running on the Windows desktop,
esCalc can run on most office computers independently without having to deploy
the extra server additionally.
esCalc resembles Excel in UIs that is easy
to learn and grasp. The overall interface is shown as the below figure:
esCalc is especially designed for the
business users without technical background. It can be installed in a common
working environment easily, and be used once installed.
Various Data
sources Support
esCalc supports various databases,
including MSSQL, Oracle, Access, MySQL, DB2, Sybase, and other mainstream
databases. In facts, esCalc supports any databases with JDBC and ODBC drivers.
Besides the access to the database in the
LAN, esCalc also supports the access to the local data file, such as txt, log,
tab, other text files, and the Excel 97~2010 spreadsheets.
esCalc also supports the interactive
calculations between various data sources, for example, to store the basic
information like the company name, the contact information, and the company
industry in the database, and to store the follow-up visit to some clients by
sales persons in another Excel spread sheet.
With esCalc, you can merge the two pieces of data easily to form a
follow-up visit log classified by the company industry. Even if their Number of
clients and the Sort Criteria differ to each other, esCalc can handle it with
easy.
The office environment of business
personnel is sometimes rather complex, such as the CRM, MIS, ERP, DSS or
performance environments, supply chain management, and other application
systems. esCalc supports various data sources and interactive calculations and
is capable to handle the complex office environment.
Step-by-step Calculation
The step-by-step calculation can decompose
the complex computational goal into several simple steps and complete a
seemingly complicated great goal by solving each simple small problem.
Still with the above example, to calculate the
stock rising for consecutive 5 days, you can group by stock first, and then calculate
the daily increment of the stock. An increment greater than 0 indicates the
stock is rising. Based on this judgment, the consecutive rising days of each
stock can be calculated out. Lastly, the longest consecutive rising days of
each stock can be calculated through filtering and sorting. The details are
shown below:
Step 1: Import the stock data from txt
file, as shown in the below figure:
Step 2: Filter out the data in the previous
month by date. Suppose it is June in 2011, as shown in the below figure:
Step 3: Set the level by the stock code:
In the above figure, the newly-built level
is in the red block on the left, and in the red circle on the right is a stock.
This row is the summery row.
Step 4: Sort the transaction data of each
stock by Date in ascending order.
Step 5: Calculate the daily growth rate of
each stock. In this step, a calculation column D needs to be added, and a
formula =(C4[A2]-C3[A2])/C3[A2] is
entered to the D4. The formula will be pasted to the related cells
automatically, as shown in the below figure:
Step 6: Compute the consecutive rising
days. In this step, a computation column E needs to be added, and then the
formula in E4: =if(D4>0,E3+1) to
be entered. The result is shown in the below figure:
Step 7: Calculate the longest days of a
certain stock rising consecutively by inputting the formula like max({E4}) in E2. The result is shown
below:
Step 8: Fold the summary row by clicking on
the level number 1 on the left, as shown in below figure:
Step 9: Sort by the longest consecutive
rising days, as shown in below figure:
In the above figure, 5 stocks keep rising
for 5 consecutive days in June 2011, which are American Express Co., The Boeing
Co., Citigroup, Inc., General Motors Corp, and Coca-Cola Co. It is certain that
the data can also be filtered by the longest consecutive rising days, as shown
in the below figure:
To solve the relatively complex computational
goal in this case, we decompose the computational goal into 9 steps of simple
operation or formula computing.
The step-by-step computation allows users
to decompose, simplify, and ultimately solve problems in a rather visual train
of thoughts. Owing to this, business users can also solve some complex data
computing problems by themselves.
Adequate data
computational capability
esCalc is powerful enough to handle the
various computational task in the daily office work.
Function
as SQL in every aspect
With the same computational capability as
SQL, esCalc can be used to filter, group, sort, and perform the distinct,
union, join, and other equivalent actions of SQL. Please refer to the menu shown in the below figure:
No
technical background required
SQL can only be grasped by the professional
technician, while esCalc represents SQL functions with graphics and decomposes
it into several steps so that even the business users without technical
experiences can handle it easily. For example, in the step 3 of “building levels
by stock code”, esCalc does not require any complex coding, and users only need
to set it up in the menu easily.
Alternatively, the default shortcut menu can also be used, as shown in below figure:
Because SQL does not support the
step-by-step calculation, SQL solution to this case will be lengthy,
error-prone, and hard-to-understand statements. Obviously, it is hard for the
business users to grasp.
Computational
capability beyond Excel
The Excel and other tools alike do not
provide the auto copy and intelligent adjustment functions. The similar
functions can only be implemented with a great amount of manual operations. For
example, the longest consecutive rising days of each stock, esCalc users can
enter the formula =max({E4}) in E2 directly, as shown in the below figure:
After entering the formula in E2, all
homocells of E2 will be populated with the formula, for example, E94. By
comparison, Excel does not provide the auto-copy function, and you will have to
conduct it manually. In addition to the auto-copy, esCalc also supports the
intelligent adjustment function, for example, the formula in E94 will be
adjusted to =max({E96}) to meet the
business logics, as shown in the below figure:
In addition, the homo-cell model maintains
the business relations between esCalc cells, so that the true grouping is implemented,
and the grouped data can be further processed. This is hard to be implemented
with the Excel and other tools alike, for example, in the step 4 of the above
case, “sort the dealing data of each stock by Date ascendingly”. For Excel,
ungrouping is required to sort the data by stock & date and then group.
However, with esCalc, you can sort directly to implement it.
Multiple
computational functions
esCalc can be used to perform the complex
computations related to sequence numbers. For example, still in the above
example, calculate the rankings of closing price of the end of previous month
on the basis of the result in step 8.
Firstly, calculate the closing price in the
end of month, then append a new column F, and enter the new formula in F2: ={C3}.m(-1), as shown in the below
figure:
Then calculate the rankings. Append the new
column G, and input the formula ={F2}.ranki(F2)
in G2, as shown in the below figure:
As can be seen from above, 9N12584 (i.e. IBM)
has the highest closing prices among these shares of more than 20.
esCalc can also perform the intersection,
union, compliment, and other set operations, for example, compute the stocks that
are among the last 10th (cheaper) by closing price and rising for
consecutive 4 days. esCalc can also be used to perform the inter-row computations
such as monthly year-on-year comparison and the link relative ratio
comparison, for example, compute the stock price moving averages in
the 5 days.
In conclusion, esCalc is a typical desktop
application which is able to support multiple data sources and step-by-step
computation with sufficient data computational capability. It is the data
computation tool for business users to handle the various computational
problems in the daily office work easily.
No comments:
Post a Comment