Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
nougatitati
Contributor III
Contributor III

Aggregation by sum of minutes

Hi,

I have this table of dates with associated time values in minutes and seconds.

clipboard_image_0.png

I want to change this table group by "day of entry" and "Unit" and have a sum of the third field. To do this I tried to change the table from what gave the image above:

clipboard_image_3.png

to this

clipboard_image_4.png

but this results in this table:

clipboard_image_5.png

Which has times which I'm fairly sure are incorrect. What could be going wrong?

EDIT:For extra information, I have counted over 30 entries all with at least 3 minutes for GQ 18/07/2019, but it's summing to only 44:20 in the image above. That's where my intuition comes from.

2 Solutions

Accepted Solutions
marcus_sommer

Time() could only display (not calculate) values up to 59 minutes. Therefore just replace it with interval().

- Marcus

View solution in original post

marcus_sommer

I'm not very familiar with the data manager but I don't think that's the right place to do an in depth validation and/or an analysis of the data. I would regard it more as a kind of advanced pre-view.

At least if the results didn't fit the expectation I would pull the related data with an unique key field (if none exists it could be created in the script with recno/rowno) into a table in the UI to see which data are really there and parallel a more aggregated table which sums and counts the data - with both any strange results which might be caused by any NULL's , missing values, poor data-quality, missing/wrong table-associations and similar are then quite easy to spot.

- Marcus

View solution in original post

5 Replies
marcus_sommer

Time() could only display (not calculate) values up to 59 minutes. Therefore just replace it with interval().

- Marcus

nougatitati
Contributor III
Contributor III
Author

You are right! But for some reason the aggregation is still incorrect! Here I have all the records for "GQ" on 23/07/2019 (the last entry is meant to be null):

clipboard_image_0.png

Manual inspection suggests the sum of this should be 60:57. Using the code i showed before:

clipboard_image_2.png

I get this aggregation:

clipboard_image_4.png

Which is not correct! I've verified several times that there are no GQ 23/07/2019 entries on that table that could be going into this summation that I haven't noted.

Any ideas on how to find a resolution to this?

nougatitati
Contributor III
Contributor III
Author

WOW okay I have no idea why this happened. In a desperate attempt, I turned off the filtering I placed on the time field. For context, the filter set entries above 10 minutes to null().

This resulted in a very different story in my data manager!

clipboard_image_0.png

In my last view of the data manager there were 12 entries with one set to null(). In this one there are 18 with 14 below 10 minutes.  By summing the entries  below 10 minutes manually, now, I obtain the sum value that I was getting from Qlik (i.e., 78:09). So it WAS summing, but for some reason it wasn't showing me all the records until I removed the filter.

As one last question, does qlik ever hide data entries in the Data Manager? If so, why? When?

marcus_sommer

I'm not very familiar with the data manager but I don't think that's the right place to do an in depth validation and/or an analysis of the data. I would regard it more as a kind of advanced pre-view.

At least if the results didn't fit the expectation I would pull the related data with an unique key field (if none exists it could be created in the script with recno/rowno) into a table in the UI to see which data are really there and parallel a more aggregated table which sums and counts the data - with both any strange results which might be caused by any NULL's , missing values, poor data-quality, missing/wrong table-associations and similar are then quite easy to spot.

- Marcus

nougatitati
Contributor III
Contributor III
Author

 A great point! Thanks for your insight!