How to find overlapping datetime for two different entries

Hello,

I am using a product that is built off of sisense and need to build a script so that I can create a custom function or script that will show all over lapping entries. Below is a sample row from the data I am working with.

Incident Number	Dispatched Time	Cleared Time	Unit Number
2024-1000	08/14/2024 07:00	08/14/2024 10:00	M66
2024-1001	08/14/2024 09:30	08/14/2024 12:00	M68

In this case I am hoping to return the data in one dashboard and a count of total number of overlapping incidents based on these two values in the data.

In this example there was a second incident that occurred and the first was still in progress. If this was the only overlapping call it should return a count of 1 and the above table. The table columns are already in the dashboard but show all incidents.

RobertToumbs

Posted 1 year ago·Last reply 1 year ago

8 comments

Ravid Paldi

·1 year ago

Hi RobertToumbs

This is achivable but requires some coding to be done . Below are a ew questions that would help in prividing you a direction to where and how you can achieve this:

- What is the maximal numner of rows that the Incidents table would need to process? (10 |100 |1000 |10K...?)

- Can you make an assumption on the "maximal duration of an event"? (for example, an event would never take more then 24 hours)

- I'm guessing that you have designer access to build your dashboards but do you have data designer access as well to make changes to the data model?

Looking forward,
Ravid

Paldi Solutions - Gold Certiefied Sisense Parneter

ravid@paldi.solutions

RobertToumbs

OP1 year ago

10K rows would be the upper limit. I doubt it would be that high but thinking in longevity.
Never more than 96 hours overlapping
I have complete access including designer. I am in charge of making changes to our model

Ravid Paldi

·1 year ago

Hi RobertToumbs

Another question, do you need that number to be dynamic?

Or in simple terms, do you need your users to be able to apply filters on the dasboards that would remove some of the events and then get a new value that trepresnets the overlaps without the filtered events?

Assuming yes, then your best route in high level, would be to:

- Generate an Indicator widget. It doesn't realy matter which formula you set for it so you can keep it a static 1

- Create Python script that would be used as a Custom Code Transoformation (CCT) script:

- When you set a CCT for a widget, Sisense would essentialy execute that script on the server and execute it on the widget's original query result and then it returns the scripts output to the widget. You can pass the dasboard filter selection as addtional paramters to the script

- So the idea is that you would essentially write a CCT script that would build a simpel JAQL that would rertieve all the events ordered by their timestampe and then you would process each data point by their order only for the events that had happened within the followign 72 hours.

- I reccomend moving the date to be a numeric date in epoc seconds as it would signficantly improve your perofmance and simplify your script.

- Make sure to remember to apply the dashboard filters when you pull the initital data set that needs to be processed.

- Based on the numbers that you have provided, I believe that even for 10K records the CCT script won't take more then 30-40 seconds to load tops (if written properly).

I didn't find the official documents for the CCT but note that this is a fairly advacned topic in Sisense and you also need to ask your admin to enable that feature for your Sisense server:

Good luck and let me know how it goes (:

Ravid

Paldi Solutions - Gold Certiefied Sisense Parneter

ravid@paldi.solutions

DRay

·1 year ago

Hi RobertToumbs.

I tried to find your account based on the email you signed up for the Community with, but I'm not able to find one. Can you PM me the company that you are working with to access Sisense? Then I can help find a resource to work with you.

RobertToumbs

OP1 year ago

I'm using a product called ESO (EHR/Fire incidents) it uses sisense for its reporting software. I work for the fire department and am trying to pull back data. ESO states that it is not currently possible to show overlapping time data.

DRay

·1 year ago

Hi RobertToumbs.

I discussed this with our internal teams, and we will need more information to be able to resolve this. Can you reach out to your account team to get a technical resource assigned to work with you?

RobertToumbs

OP1 year ago

ESO who uses sisense has basically said that this is not a priority and won't assign a specific analyst to help with the issue. This is an issue that their whole platform has and they are working towards backing it possible.

ESO doesn't seem to reach out other than via email and won't escalate my issue to a developer. I'm looking for work arounds which they suggested I reach out on a community board on sisense.

DRay

·1 year ago

Hello RobertToumbs ,

Thank you for reaching out here. I see that this question hasn't had a response yet, so I am reaching out internally to try and get you an answer.