SQL in Excel
Spreadsheet software made it possible for non-technical users to do programming without even realizing it. Just typing a simple formula and having all the rows automatically compute using it is nothing but magic for a non-programmer. But did you know Excel also makes non-technical users use powerful analytic SQL queries without knowing anything about SQL? Yes, and we will delve into that aspect of SQL in Excel in this page. Several Excel functions are analyzed from SQL perspective.
SQL Frames provides both Aggregate and Analytic Functions that are SQL compatible. These functions makes it easy to solve most requirements. Most of the Excel Show Values As options can be mapped to these Aggregate and Analytic functions.
The plugin is available using df.excel. Below are the list of API followed by examples.
| Name | API | Applicable To |
|---|---|---|
| % of Grand Total | excel.pogt | |
| % of Parent Total | excel.popt | |
| % of | excel.po | |
| % of Row Total | excel.port | Pivot |
| % of Column Total | excel.poct | Pivot |
| % of Parent Row Total | excel.poprt | Pivot |
| % of Parent Column Total | excel.popct | Pivot |
| Running Total In | excel.rti | |
| % of Running Total In | excel.porti | |
| Difference From | excel.differenceFrom | |
| % Difference From | excel.percentDifferenceFrom | |
| Rank Smallest to Largest | excel.rankAsc | |
| Rank Largest to Smallest | excel.rankDesc | |
| Index | excel.index | Pivot |
In order for these functions to work, appropriate aggregate rows need to be generated
using rollup and cube functions. See GROUP BY.
If Applicable To is blank it means the function is applicable to both Pivot DataFrame and Grouped DataFrame (think of it as a Pivot with no pivoted columns).
First lets setup some data.
% of Grand Total
It is easy to aggregate data using functions such as SUM and COUNT. But often we want to know
the relative performance and for that we need the % of Grand Total. This is possible using the
function df.excel.pogt() which is short for Percent Of Grand Total.
The DataFrame should be constructed using ROLLUP/CUBE clauses that generate the grand total partition.
% of Parent Total
What if you want the performance related to not grand total but a specific level of detail. This can be
achieved using excel.popt which is short for Percent of Parent Total.
% of
% of Row Total
Once we organize data in pivot tables, that opens up additional ways of analyzing data.
For example, we might want to know the percent of the row total instead of the grand total.
That is, in the following example, after organizing data by Region along rows and Sales Channel
along columns, we want to know % of revenue by Sales Channel type within each Region.
The df.excel.port() API which is short for Percent Of Row Total is used to achieve this.
% of Column Total
This is similar to Percent of Row Total described above except the percentage is computed using
the column totals. In the example below, we are interested in the percent of Sales Channel type by Region
compared to the Sales Channel type for the entire world. The API df.excel.poct() which is short for
Percent Of Column Total is used to achieve this.
% of Parent Row Total
Pivot tables can have more than 1 dimension along the rows. In such cases, we might be interested in the percentage with respect to the column totals at the parent row. So this is like Percent of Column Total but instead of the column total at the grand total row, it uses the column total at the parent row.
In the following example, we have Region and Order Priority in the rows. For rows with both Region and Order Priority
the comparison is with the column totals at the Region row and for rows with just Region the comparison is
with respect to column totals at their parent row which is the grand totals row. The df.excel.poprt() API which is short for
Percent Of Parent Row Total is used to achieve this.
% of Parent Column Total
PIVOT tables can also have more than 1 dimension along the columns. In such cases, we might be interested in the percentage with respect to the row totals at the parent column. So this is like Percent of Row Total but instead of the row total at the grand total column, it uses the row total at the parent column.
In the following example, we have Sales Channel and Order Priority in the columns. For columns with both Sales Channel
and Order Priority the comparison is with the row totals at the Sales Channel column and for columns with just Sales Channel
the comparison is with respect to the row totals at their parent column which is the grand totals column.
Using the df.excel.popct() API which is short for Percent Of Parent Column Total, this can be achieved.
Running Total In and % Running Total In
Running total or cumulative total aggregates along a specified field and its sort order. The df.excel.rti() API can be used for this
which is short for Running Total In.
Difference from and % Difference from
Difference from and associated % Difference from can be calculated by specifying base field(s)
and their corresponding base item(s). excel.differenceFrom() and excel.percentDifferenceFrom() API
can be used to calculated these values.
Rank
Ranks can be calculated smallest to largest or largest to smallest. This is done using the excel.rankAsc
and excel.rankDesc API.
Index
Use excel.index() API for computing the index of a value in a pivot table.