Skip to end of banner
Go to start of banner

Using Date Dimensions

Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 2 Next »

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:

 

 

And create the object with Cumulative selected ( I also name the object to make it clear)

 

 

I’ve used course – rather than dates because my data is a bit rubbish

 

Now split that by other dimensions

 

 

This will all work with your progression table (the snapshot in time). You can add the date dimension link to it, then select a date.

 

To show which step followed which step sequentially – we’d need to create a view.

 

This would group the data in the progression table by student, show this min max date for each step.  Constructed as a union query.  Then we can calculate days between each step.

This would be much simpler to build with you, probably a couple of hours work.

 

I have include the view script in this message as it also allows us to select date ranges nicely.

 

 

I Add date short name

 

Then join my date using a between

 

 

Now I can use this a  category object

 

 

Now in my dashboard I can select any of those periods

 

 

 

 

 

 

 

 

  • No labels