Using Date Dimensions

Owned by Karen Hogan (Unlicensed)
Last updated: Jul 07, 2022
3 min read

There are 2 scripts at the bottom of this page which can be used to:

  • Build a table of all dates between 01-Jan-2000 and the 31-Dec-2100

  • Create a view that we can use to select date groups i.e. this week, last week etc.

 Each of these scripts can be extended if needed.

You can run these scripts in your DB and then use in piDashboard.

Select the objects that you want the user to be able to use in the date dimension table, in the following screenshot you can see that we’ve added every column.

Create a join between this table and any other table that you want to query using dates. In the following example, we’ve created a join between a field called ‘Course_start_date’ to the field called ‘Date’ in the calendar table.

You can now use this object to create useful charts in the dashboard.

Working out the difference between two dates

To get the difference between two dates in MS SQL you could use a DATEDIFF function.

In this example, we’re going to work out how many days it took for students to get a disciplinary after the start date of the course. We’ll be using two fields to achieve this:

  • Course_Start_Date

  • Date_of_first_Disciplinary 

In the database, we created a manual object that works out days but you could easily change this to weeks, months or years : 

In the dashboard, we can now produce a chart that will show the required information.

Showing a cumulative count 

To do a cumulative count, you could create a manual object like the one shown below:

The chart based on this object would look like this:

To show which step followed which step sequentially – we’d need to create a view. You can find the ‘View’ script at the bottom of this page. This would group the data in the progression table by student, show this min max date for each step.  Constructed as a union query, we can then calculate the days between each step.

We added date short name and then joined the date using a between

We could now use this as a ‘Category Object’

which would allow us to select any of these dates in the dashboard