cancel
Showing results for 
Search instead for 
Did you mean: 
intapiuser
Community Team Member
Community Team Member

 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:
mceclip0.png

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:
 mceclip1.png
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
mceclip2.png
  
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.
mceclip3.png
 That's it!
Rate this article:
Version history
Last update:
‎02-15-2024 02:13 PM
Updated by: