Forum Discussion

EMS_Data_Guy's avatar
EMS_Data_Guy
Cloud Apps
11-27-2025

Sort 'sdiff' results

I'm trying to figure out how to calculate the elapsed time between two data points in a record and then sort the results into different buckets. We use an embedded version of Sisense so I can't edit anything via the 'Elasticube'. 

I'm trying to take all the records created by a person and calculate the time from when the record was created to the time it was completed. Then display how many records were completed within different time frames. Either by using a column chart or pivot table. The problem is that the 'sdiff' function requires an aggregation. I don't want to find the median or average, I want to calculate the time for every record.

I tried slapping an 'ALL' in front of the 'sdiff' function but that gave me an error.

I also tried (COUNT(RECORDS),(SDIFF(ENDTIME, STARTTIME))) in the hopes that I could then filter the results but get an unexpected token error.

I also tried CASE WHEN (SDIFF(ENDTIME,STARTTIME) < 7200 THEN 1 ELSE 0 END but ran into the same problem of 'sdiff' requiring an aggregator.

What do?

 

formulas

3 Replies

  • OleksandrB's avatar
    OleksandrB
    Sisense Employee
    12-04-2025

    Hi,

    Thank you for your question. To use the DDIFF function, you should wrap it in f.e. aggregation.
    AVG(DDiff([Days in Discharge_Time],[Days in Admission_Time]))
    or SUM.
    You can provide an example of the data set and expected result, and I will try to provide a solution for you.

    • EMS_Data_Guy's avatar
      EMS_Data_Guy
      Cloud Apps
      12-15-2025

      The vendor has a prebuilt calculation for this but unfortunately it doesn't work in our specific use case as it skews the data. I can use their calculation to show you my desired output though. I'm looking to create two widgets that bucket chart completion times. 

      The first would be an aggregated column chart that looks at all records. It would look like this.

      Here is how it is built

      That "Time to Locked Status" is the prebuilt calculation I can't use and needs to be replaced by an SDIFF function. Each value in the chart is sorted by right clicking on "Time to Locked Status" and putting a filter there.

      The problem is that I can't aggregate the SDIFF with an Average or Median. I need it to look at the SDIFF for every record and then filter the results.

      The second widget is essentially the same construction just in pivot table form with the rows showing each individual person's performance.

  • DRay's avatar
    DRay
    Sisense Employee
    12-12-2025

    Hi EMS_Data_Guy​,

    I’m following up to see if the solution offered by OleksandrB​ worked for you.

    If so, please click the 'Accept as Solution' button on their post. That way others with the same questions can find the answer. If not, please let us know so that we can continue to help.

    Thank you.