Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Sum of Time Intervals

Hi All,


I am having trouble with summing up time intervals. My data has the start and end time for an employee and I have calculated the gap between the time using the Interval function.

Furthermore, each employee is also assigned a warehouse number and I am trying to sum up the gap per warehouse number. I find that the number I get is usually double the actual number that I've manually calculated on Excel, meaning there is an obvious error.

An example of the data is:

Warehouse #Employee #Scan inScan OutGap

1

5611:00:0011:09:009
15511:30:0011:46:0016
24511:35:0011:40:005
27511:46:0011:50:004
38911:56:0011:57:001
37811:58:0012:00:002

Naturally, the correct data would be:

Warehouse#          Sum of Gaps

1                         25

2                         9

3                         3

However I am getting exactly four times the expected values. Does anyone know why this occurs and how to fix the issue?

Thanks and I appreciate any reply greatly.

1 Solution

Accepted Solutions
marcus_malinow
Valued Contributor III

Re: Sum of Time Intervals

Hi Kevin,

I think we'd need to see your data model to determine exactly what the issue is, but it sounds like there's some duplication in your data. Maybe you have duplicate employee or warehouse records. If these are being used in your chart then that could account for incorrect results.

If you're not able to share your data model, I'd suggest making your dimension loads LOAD DISTINCT.

Marcus

8 Replies
marcus_malinow
Valued Contributor III

Re: Sum of Time Intervals

Hi Kevin,

I think we'd need to see your data model to determine exactly what the issue is, but it sounds like there's some duplication in your data. Maybe you have duplicate employee or warehouse records. If these are being used in your chart then that could account for incorrect results.

If you're not able to share your data model, I'd suggest making your dimension loads LOAD DISTINCT.

Marcus

Not applicable

Re: Sum of Time Intervals

Hi Marcus,

Thanks for the reply, appreciate it!

The LOAD DISTINCT works! However this prompts me to wonder why there are duplicates in the data as when I create a Table Box, I don't see any duplicates. I remember reading somewhere on the forum and also seeing first-hand that the table box excludes duplicated rows - is this true?

If it is true, what is the best way of seeing what the loaded data actually looks like? As I've been using Table Box to have visibility of the data tables.

Thanks,

Kevin

Re: Sum of Time Intervals

Yes, table box only shows the distinct combination of fields you added to your table box object. To view everything, I usually add all rows to a straight table and add 1 or Count(FieldName) as the expression. This won't remove duplicates.

HTH

Best,

Sunny

Not applicable

Re: Sum of Time Intervals

Hi Sunny,

When you say add all rows, do you mean add all fields as a dimension? Could you clarify more on what you mean by "Add 1"?

The Count method works fine but it only shows how many duplicates there are, right? It doesn't actually output the duplicates as another row.

Thanks,

Kev

Re: Sum of Time Intervals

When you say add all rows, do you mean add all fields as a dimension? Could you clarify more on what you mean by "Add 1"?

Your expression needs to say 1

Capture.PNG

Not applicable

Re: Sum of Time Intervals

awesome, thanks very much!

Thank you Marcus.Malinow‌ as well for helping me with my previous query

Re: Sum of Time Intervals

No problem bud.

I am glad I was able to help.

Best,

Sunny

aaryanqvdevelop
Contributor

Re: Sum of Time Intervals

try this..

Select Dimension as Warehouse and in expression give :=Aggr(Sum(Gap),Warehouse)

Community Browser