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:
But I received a syntax error in the Sisense widget editor.
Eventually, I found the correct approach using IF() instead of CASE:
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!
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.
(SUM(trip_duration_minutes),([shift_alignment])/(SUM(trip_duration_minutes),all([shift_alignment]))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
Feel free to subscribe to our newsletter to stay up to date with the latest QBeeQ news
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:
