Excel Dashboard : Create Summary View (portlets)

In the previous article (Create your first excel based dashboard) we created our first dashboard and now we continue adding more content to it.

Create Summary Portlets

Using the same data, we can create any slice of data by grouping the numbers by any column. Click on the new Qlet button and type the following query

By Region
select country_region, sum(quantity_sold) as qty_sold,
sum(amount_sold) as amt_sold
from [detail_data$]
group by country_region
order by 3 desc

“order by 3 desc” tells to sort by the third column in descending order. You can also mention the sort this way “order by sum(amount_sold) desc” or “order by sum(amount_sold) asc” for ascending

We will add few more portlets as shown below.

By Product Category

select prod_category, sum(quantity_sold) as qty_sold,
sum(amount_sold) as amt_sold
from [detail_data$]
group by prod_category
order by sum(amount_sold) desc

By Channel

select channel_class, sum(quantity_sold) as qty_sold,
sum(amount_sold) as amt_sold
from [detail_data$]
group by channel_class
order by sum(amount_sold) desc