March 2, 2010 – 8:40 pm
Previous Article – Create Excel Dashboards – 4
Add Date Hierarchy Columns
Most of the data, has some form of date information. Now date is very granular information. Very often, to generate trending information, you need to derive year and month columns.
In the dashboard designer, you can easily generate Date hierarchy columns for any kind of reporting.
In our example, we have order date, so we will generate the Year, Quarter, Month, Day , Week and other flavors of the same information for better representation.
Right click on the “Order Date” column and navigate to Create ‘ “Add Year, Quarter, Months column
Click on “Add” and it will add the following columns to the right of the primary column “Order Date”
You may not need all of the date derived columns but it is worth understanding what they stand for.
The derive columns, have a prefix of “Or” which are the first two letters of the primary date column “Order Date”. The remaining part explains the actual part of the date.
1. Year : represents the year component of the date
2. Mth N : represents the numerical month.
3. Mth Disp: represents the month number but padded with zero on the left
4. Mth NN: represents month number and 3 letter month name
5. Yr Mth : represents the year and month number
6. Qtr: represents the quarter number
7. Week : represents the week of year
8. Dy: represents day of year
9. … and so on.
The concept of date hierarchy even though simple to implement is very powerful. Within few minutes you can derive important “Insight” into your data. We will now begin the process of deriving intelligence out of boring data. Now begins the fun part.
Create Date Filter and Parameters
If you work with real transaction application, you already know that the amount of data is huge and the data has some form of date column. This date could be transaction date, invoice date, order date, payment date etc.
One of the dashboard and reporting best practices is to have some form of date filter and/or parameters so users can dynamically choose the date range. This helps in narrowing down the data for analysis.
Let us create a simple date filter.
One of the typical requirement is to see data for “as of today”.
In the dashboard designer it is few click process to implement this.
Right click on the Date column (order date), Navigate to
Create ==> Create Date Filter
The detail use of the Date Filter is covered in a separate tutorial. As we have very minimal use of the date filter for the dashboard that we are building we will just touch the basics.
In the date filter, on the left you see the column name and on the drop down you see different options such as below
” First Day of Month
” Last Day of Month
” First Day of Qtr
” Last Day of Qtr
” First Day of Week
” Last Day of Week
” First Day of Year
” Last day of Year
For our purpose we just limited our data to be less than today.
Click on “use this filter” and it will be applied to the Qlet.
One of the best practice is to avoid hard coding of any values in the Query. For e.g, The dashboard designer allows you to build drop down list of values as parameters to the query. We will leverage this option to the fullest so that the dashboard is extremely useful and can respond to data changes.
We will create a From and To Date range parameter
Right click on the “Order Date” column, Navigate to Create ‘ Create Parameter
It instantly creates two Data Parameters
We arrange them and size them appropriately as below
Once arranged and formatted, you can type the date or popup the date picker by click on the button on the side
Now, right click on the Title and select “Refresh”
Change the From and To Date values and refresh, the Qlet will change the data accordingly.
Next – Create Excel Dashboards – 6
Posted in Excel, How-to | No Comments »
