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
0 Comments