This example drills into the details for the current week. This is a complementary set of reports to the Weekly Metrics Report. In this example, we will focus on the most recent week and explore the top 10 product groups by sales, sales percentage growth, and sales net change. We'll see how the top 10 in each metric performs relative to the other metrics.

Query the database

We start with a single SQL query to the Kognitio WX2 server which is both very fast and can read billions of records in seconds.

The entire nextanalytics script is included at the bottom of this page

Script lines 2 through 5 set up the SQL query. Note that we embedded it in the script for the purposes of making this web page self-contained.  In a production environment, a separate .SQL file would be recommended because it keeps the business logic distinct from the database logic.

The results of the query create the first nextanalytics 'page'. The page has product groups, sales week number, and a total sales column. 

Pivot the date dimension to the columns

With one single line of script (script line 9), the text column 'saleweek' is swapped to the column dimension. This is a pivot operation that creates a crosstab. Every unique value of 'saleweek' becomes a column in a new second page. Notice that there was no requirement to predefine the column values, no complicated SQL syntax, no call to a hidden procedure. Also note that there was no reference to any specific value of week, making the report immediately useful for subsequent weeks and ideal for adhoc reporting.

Since our report is only about the most recent week, we select and keep only those two columns (lines 9 and 10 in the script). While we could have changed the SQL query to perform this filtering, we simply reused the query from the previous example. This helps to reduce the portfolio of queries being maintained in the environment and greatly reduces the overhead of the solution in production. It also demonstrates how nextanalytics makes an ideal adhoc analysis tool, using the data you have available.

Prepare product group sales column

The first column we want in the final report is the product group sales for the most recent week, so we sort it descending and change the column caption accordingly.

Calculate growth

With line 17, we calculate the percentage growth.  Then we rename the columns.

Calculate the net change

With line 21, we switch back to the previous page, and then calculate the net change and rename the columns.  

Assemble the final report

We now have all the data we want but it is spread over three pages. Script lines 26 through 33 move the columns together in the desired order on a new page. nextanalytics automatically aligns the rows, so even if the three pages contain different product groups in different orders, the resulting page will be correctly combined.

Now that we have the three columns of interest on the same page, we're ready to perform various sorting and alerting for the final three reports.

Rank each of the columns 

In each of our final reports, we will sort one of the columns and then rank the values in each column, assigning the number '1' for the largest value, '2' for the next largest and so on. This rank page forms the basis for our alerting (highlighting) operation. 

Prepare the first report page

In the first report (script lines 36 through 44), we sorted the Sales column, descending, and the alerting highlights the top 10 rankings for each column.

You can see in the report below that, of the top 10 by sales, only the 'Medical' product group placed in the top 10 of the Growth or Net Change rankings.

Continue with the second report

In the next report (script lines 46 through 58), we sorted the Growth column, descending, and the alerting again highlights the top 10 rankings for each column.

You can see in the report image below that, of the top 10 by growth, there are a lot more similarities with the Net Change rankings than with Sales.

Keep going with another report from the same data

In the third report (script lines 60 through 74), we sorted the Net Change column, descending, and the alerting highlights the top 10 rankings for each column. 

Three reports from the same data, three different perspectives. One database query. 

Summary and complete script listing

As you can see, it is easy to use nextanalytics to generate multiple reports from a single query, allowing the power and speed of the Kognitio WX2 database to be leveraged while easily delivering multiple analyses without having to learn more advanced SQL syntax, or even generating a unique query 9since we reused the query from another example).

As you can see in the listing below, the nextanalytics script langauge is very simple and easy to read. This was, after all, a simple analytical example.