...
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.
I’ve joined Course_start_date – but that could be anything – and you can add multiple alias’s if you want to join the date dimension to multiple date columns. (You will need to name your date objects carefully then)
...
You can now use the object as I see fit.
I can then outer join this so I get ‘Missing dates’
My data isn’t that great! So I only have students starting in two months – but I want to show all.
I make the join an outer join (mine is RIGHT)
You can use the objects to do all manner of comparisons.
this object to create useful charts in the dashboard.
Working out the difference between two dates
To get the difference between two date dates in MS SQL – I’ll be honest I’ll google it (I google everything to do with SQL)
This gives me this.
In my DB – I’m going to find you could use a DATEDIFF function.
...
In this example, we’re going to work out how many days it took a student for students to get a disciplinary
I create a manual object
So my calculation is like
Note: I’ve done day, but it would be soooo easy to do month, weeks, years etc.
Now let’s plot the bad behaviuor
So we can now see what’s happening
I don’t have time in application in my data set – but I can use this now for average time to be bad.
I can now use this in my dashboard
People doing animal care are pretty bad!! (Or the tutor likes giving detentions)
To do a cumulative countafter 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)
...