Hi everyone,I'm working on a widget in Sisense that analyzes driver activity, and I'm trying to calculate the percentage of each driver's total trip time that occurred during their scheduled shift.I have the following fields:shift_alignment – categorizes each trip as "Before Shift", "During Shift", "After Shift", or "No Schedule Found"Total trip_duration_minutes – the duration of each trip in minutesI want to build a custom formula that calculates:(Total trip time during shift) ÷ (Total tr...

Formula for % of Day Driven Within Shift | Help and How-To

Formula for % of Day Driven Within Shift

Hi everyone,

I'm working on a widget in Sisense that analyzes driver activity, and I'm trying to calculate the percentage of each driver's total trip time that occurred during their scheduled shift.

I have the following fields:

shift_alignment – categorizes each trip as "Before Shift", "During Shift", "After Shift", or "No Schedule Found"
Total trip_duration_minutes – the duration of each trip in minutes

I want to build a custom formula that calculates:

(Total trip time during shift) ÷ (Total trip time) × 100

Initially, I tried something like this:

( SUM(CASE WHEN [shift_alignment] = "During Shift" THEN [Total trip_duration_minutes] ELSE 0 END) / SUM([Total trip_duration_minutes]) ) * 100

But I received a syntax error in the Sisense widget editor.

Eventually, I found the correct approach using IF() instead of CASE:

( SUM(IF([shift_alignment] = "During Shift", [Total trip_duration_minutes], 0)) / SUM([Total trip_duration_minutes]) ) * 100

I'd appreciate any feedback or suggestions—especially if there's a more efficient or dynamic way to do this in Sisense.

Thanks in advance!

danielrabinovit

Posted 1 year ago·Last reply 1 year ago

4 comments

danielrabinovit

OP1 year ago

Thank you, Ido!

Ido Darnell

·1 year ago · Edited

danielrabinovit happy to hear it helped you!

You can create a top X ranking filter within the measured value by applying the top filter and then setting the top X value and by what KPI you want to apply it, see screenshot below:

top measure.png

Ido Darnell

·1 year ago

Hi danielrabinovit

This is a classic case for a measured value which is a formula syntax in Sisense that allows you to filter a specific value or calculation. You can then create more complex form,ulas based off of multiple measured values and this way to basically create a % calculation where the numerator is filterewd differently than the denumerator.

In your case you will need to create a formula which filters the numerator to "During Shift" and the denumerator to ALL shift_alignment types or values.

<span>(SUM(trip_duration_minutes),([shift_alignment])/(SUM(trip_duration_minutes),all([shift_alignment]))</span>

Then you can use this formula in any widget, and group it by driver, location, time or any other dimension you like.

Note that the numerator [shift_alignment] section will need to be filtered to 'During Shift' this is explained in the documentation.

See this article for more specific explanations and I think exactly fits your use case (I actually wrote this article a while back when I used to work at Sisense 😅)

If you're looking to group it or create yet another nested query within this logic, you may need to use a multi-pass aggregation

If I can clarify this in any way please let me know, do not hesitate to reach out

We're always here to help 😀

Ido from QBeeQ

QBeeQ - Gold Sisense Partner

info@qbeeq.io

Feel free to subscribe to our newsletter to stay up to date with the latest QBeeQ news

danielrabinovit

OP1 year ago

Hi Ido,

Thanks so much for getting back to me.

This works perfectly. But how can I make it to filter just the top 10 values on that formula?