Previous – Excel Dashboard Tutorial – 6
Create Bar Chart
Bar charts reveal important trend in your data. The very important reason we split the date into year, quarter, month, week and day components is to see if there is a trend in the sales or order information.
Right click on the Qlet title (”Name” ), and then click on the “Create Chart”
It opens a “Chart Factory Wizard”
1. The top drop down contains a list of all the chart types that are supported.
2. The X-Axis drop down has all the available fields from the Qlet
3. The Y- Axis drop down has all the available fields from the Qlet
4. The Y- Aggregation has (SUM, COUNT, MAX, MIN,AVG) values
The process is to pick the base column that will form the X-axis, the Y-axis is the numerical value column that will be used to plot the bar or appropriate chart type. The y-aggregation is required, usually we should pick “SUM” but you could use “AVG” or other aggregations as you see fit.
In our case, we want to see a “year over year trend for our sales growth”
1. Select “Bar” as the chart type
2. Select “Or Year’ as the X- Axis
3. Select “Amount” as the Y- axis
4. Select ‘Sum’ as the aggregation (by selecting sum, we are telling it to sum the amount and group it by year value)
5. Click on the “Create Chart” button
6. It shows the Glet Editor for your review which is a pre-built SQL query.
We change the Glet Name, and click on “full apply” button
It creates a bar chart as shown above, you can drag the borders and expand the view, it will scale automatically.
We just dragged the corners and expanded the view. Do not close the chart wizard yet, we will create other views like a rubber stamp.
By Quarter
Go back to the chart factory and only change the X-Axis column to ‘quarter’ field
Click ‘Create Chart’
Change the name to “By Quarter” and click “Full Apply”
Do not worry about the size and the placement. Just drag by the title “By quarter” and place it in a position where you can see it. At the end we can re-arrange the portlets on the dashboard canvas.
The column “Or_Qtr_Disp” has Qtr, 2 digit year and the last digit indicates the quarter number. The reason we chose this value is so that we can order the quarter across multiple years.
As you see, we can clearly see a trend emerging from our data. The Qtr-98-1 has the tallest bar. Now lets create a month view so we can nail down, which month contributed to that spike
By Month
We go back to the chart factory and select “Or_Mth_Yr_NN” which contains Year, month number and month name(3 letter).
So far the tallest periods are March and April of 1998. Why is that? Did you do special promotion, did you get extra word of mouth or publicity?
Similarly you can create slices by Week, or each Day. Note that as you go more granular, the chart may look more congested.
Now let us see which products are doing better.
Click on apply
Change the name to “By Product” and in the SQL query below, type, “Order by 2 Desc” which will sort the products by the revenue in the descending order
You have now found your winning product line.
But lets see how each of the products fair well within their own category.
Next – Excel Dashboard Tutorial – 8
