Presenting Time-frames in a Scatter Chart
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 DrugApprovalDatesStep 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.
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!
Updated 02-15-2024
