Page Comparison

...

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 count

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.

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.  Then , we can then calculate the 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

...

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

Now I can use this a  category object

...

We could now use this as a ‘Category Object’

...

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

...

...