topic Re: Elapsed time approximation error? in App Development

Elapsed time approximation error?

steax — Mon, 23 Oct 2023 10:42:50 GMT

Hi Everyone!

I'm running into a "weird issue", when I calculated elapsed times between date times / timestamp values seems that I run into an approximation error.

I've prepared the following sample script to demonstrate what I mean, hoping that some one could indicate what I'm doing wrong here.

The sample script is supposed to load 10000 records with the same start and end date and time, and then calculate the total elapsed time in seconds among all the records.

The start is always: 2023-09-01 00:00:00, end is always: 2023-09-01 00:00:05 (+5 seconds from start)

The total elapsed time should be 50000 seconds (10000 records * 5 seconds each), instead I get 49999:

Sample script:

Set i = 0; Do while i < 10000 TimeStamps: Load Timestamp#('2023-09-01 00:00:00', 'YYYY-MM-DD hh:mm:ss') As EventStart , Timestamp#('2023-09-01 00:00:05', 'YYYY-MM-DD hh:mm:ss') As EventEnd AutoGenerate 1; i = i + 1; Loop Elapsed: Load Sum(Interval(EventEnd - EventStart, 'ss')) As ElapsedSeconds Resident TimeStamps;

Thanks for your time

Re: Elapsed time approximation error?

marcus_sommer — Mon, 23 Oct 2023 13:57:45 GMT

I assume it's related to the used binary number-system of Qlik which could lead to Rounding Errors - Qlik Community - 1468808.

Beside the question why you want to create n equally records the above tasks doesn't need an outside-loop else might be just:

TimeStamps: Load Timestamp#('2023-09-01 00:00:00', 'YYYY-MM-DD hh:mm:ss') As EventStart , Timestamp#('2023-09-01 00:00:05', 'YYYY-MM-DD hh:mm:ss') As EventEnd AutoGenerate 10000;

Re: Elapsed time approximation error?

hic — Mon, 23 Oct 2023 14:07:49 GMT

As Markus points out, it's a rounding error.

2023-09-01 00:00:00 = 45170.000000
2023-09-01 00:00:05 = 45170.000058

So we are talking about a difference between the two numbers in the 10th significant digit, when the matissa just has 15 digits. Adding 10000 numbers will make the rounding (in the 15th digit) visible.

If you instead calculate
Interval(Sum(EventEnd) - Sum(EventStart), 'ss') As ElapsedSeconds
you will get 50004, which shows how large the rounding error can get.

HIC

Re: Elapsed time approximation error?

steax — Tue, 24 Oct 2023 15:43:55 GMT

Thanks both, the article was indeed interesting. The rounding error I can observe in one of my analysis (digesting millions of events) is negligible from and end user prospective, I just wanted to go at the bottom of it to understand what was causing it.

Again thanks for your explanation and have a nice day!