When building a dashboard, we need to help the viewer to see patterns in the data. With Nextanalytics, the flexible alerting capability allows us to easily highlight the top 10 items in multiple columns, immediately providing business insight. In this example, we'll look at top selling products by sales, as well as by percentage growth and net change over the previous period. Outstanding performers are high in all three.

Start with a database query

With a simple SQL query, we obtain the summary sales by product group for various weeks. Although this query returns more information than we need (it includes sales since the beginning of the year), we were using it for other charts on the dashboard and can build all of them from a single query. This reduces the load on the database server as well as the effort maintaining unique queries.

Then we use a simple Nextanalytics operation to convert it to a crosstab. Because we are seeking to calculate the Growth and Net Change from the previous period, we only need the last two weeks of data.

Some simple analytics

Using this data, we use two easy operations to calculate the percentage growth and net change for the last period, and we then combine the results into a single page. While the numbers are all there, it forces the reader to search for insight, so let's continue.

Rank the columns 

In our dashboard, we want to show the results showing the top product groups by sales, by growth, and by next change. In each case, we want to highlight the top 10 for each of the columns. We do this by first ranking the values in each column, assigning the number '1' for the largest value, '2' for the next largest, and so on.

We can now use those rank values to supply color-coding on the dashboard reports (orange is higher, blue is lower). With Nextanalytics, we can generate three sorted tables from the one query -- showing the top sales, top growth and top net change product groups.

This technique makes visible the fact that top values in a certain column aren't necessarily the top values in other columns. Ideally, in this example, you'd look for rows which were top in all three.