the first version counts the number of cases where the thoughput time between the first
From and the first
To activity is smaller than 1 time unit. You can change the formula depending on what you’re trying to achieve.
Most likely, you get a different value than expected due the following reason (I assume that you’re always using
MINUTES as the time unit):
REMAP_TIMESTAMPS returns an integer, so only full minutes. Now, if there are for example 1 minute and 40 seconds between 2 activities, because of you are using full minutes, this results in a throughput time of 1 minute, which is what you count. However, you probably don’t want to count those. You could fix this by using
SECONDS as time unit and compare it to
Another reason might be that if you have multiple occurrences of
From activities, you only count the case based on the first pair, although the second pair might be interesting for you as well. Or you don’t want to calulate the KPI based on the case level at all, but more on the “activity-pair” level?
Your second variant (returning 0 instead of NULL in the ELSE) doesn’t make sense, it basically only counts all cases which contain at least one
From and one
To activity. If you want to use 0 instead of NULL here, you need to use
SUM instead of
COUNT (but the first solution is more efficient)