topic Re: Sum function in a certain amount of time in QlikView

Sum function in a certain amount of time

dominicanlauf — Wed, 20 Feb 2019 12:45:50 GMT

Hello everybody,

I want to calculate the running time of my machines. But I would like to sum up the production time only up to a certain time. For example, I would like to calculate the running time of the machine for the last week - but only from 6 to 21 o'clock.

I add a picture to clarify the problem.

Currently my formula is Sum (LatheStatistics.Duration) * 24

So I calculate the total runtime, but now I would like to add only the values from the right table (Sum_if) if the time is between 6 and 21 o'clock.

Thanks in advance!

How should the formula be? My biggest problem is the syntax.

Re: Sum function in a certain amount of time

mato32188 — Wed, 20 Feb 2019 15:03:08 GMT

Hello Dominic,

I would suggest you to do 2 things:

In script create a flag like

if(END_TIME-floor(END_TIME)<=0.875 AND START_TIME-floor(START_TIME)>=0.25 AND END_TIME-START_TIME< 1,1,0) as Is_WithinTime

to match 6-21 interval within 1 day.

In expression editor

Sum ( {<Is_WithinTime = {1}>} LatheStatistics.Duration) * 24

Martin

Re: Sum function in a certain amount of time

Gysbert_Wassenaar — Wed, 20 Feb 2019 16:00:02 GMT

I think you should split the start and end time fields into separate fields for the data and the time:
Date(Floor(LatheStatistics.START_TIME)) as StartDate,
Time(Frac(LatheStatistics.START_TIME)) as StartTime,
Date(Floor(LatheStatistics.END_TIME)) as EndDate,
Time(Frac(LatheStatistics.END_TIME)) as EndTime,

That's a good idea anyway because it will use less memory that way. But you can the also easily create a marker field in a preceding load for those records that have a time between 6 and 21 o'clock:
if(StartTime>=6/24 and EndTime <21/24, 1, 0) as _Flag6To21.

Once you have that flag field you can use it in an expression to filter the records:
Sum({< _Flag6To21={1}>} LatheStatistics.Duration) * 24