Presenting Time-frames in a Scatter Chart

[intapiuser]

 Analytical Need 

For different use cases, it can be relevant to present a timeframe of different business entities and the relations between them.  

For examples, such use cases can be:

• Present hotel vacancies by room number or type
• Analyze ticket load per agent by presenting the start and end date of every ticket
• Tracking project progress by presenting the tasks somewhat like in a Gantt chart
• Drug approval period in a particular country

Use Case - Drug Approval Period

To demonstrate the solution Let's focus on the Drug Approval Period use case.

Every drug has a start-date that represents the submission of the approval request and an end-date representing the approval date of the drug.

For example, here is our data structure of table DrugApprovalDates:

Solution

Step 1: Modeling the Elasticube

Start by making sure the data is in an appropriate structure.

For this solution, there will need to be only one Date column to, later on, use in the widget.

Change the data structure to the following format:

 

The SQL script used to create this format:

SELECT Drug, StartDate Date, 'Start Date' AS DateType

FROM DrugApprovalDates

UNION

SELECT Drug, EndDate, 'End Date' AS DateType

FROM DrugApprovalDates

Step 2: Creating the Widget in the Dashboard

Now we can create the Approval Period widget:

• Use the Date field both on the X-Axis and Break By/Color sections
• Use the Drug field as the Y-Axis

After creating the widget, the only thing left is to connect between the dots of the break by (a Drug in our case) with a line that will help present the timeframe length.
For this, we will use a widget script described in the Connecting Points In Scatter Chart By Dim article by the amazing Artem Yevtushenko.

 That's it!