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
In these use cases, there would usually be a start-date and an end-date but we would like to represent in a visual way how they correlate to one another.
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:
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
SELECT Drug, EndDate, 'End Date' AS DateType
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.