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: 
Emmanuelle__Bustos
Partner - Specialist
Partner - Specialist

Avg(TimeValues) with not subsequent days

Hi Community,

The Results desire and undesired are in the excel attached.

can someone please give me a hand to understand the unwanted result?


Attached a qvw sample, please select 02/06/14 to 09/06/14 from listbox to match with the excel sample.

1 Solution

Accepted Solutions
rbecher
MVP
MVP

..or the other field:

=time(avg(frac(HORA_SALIDA_HHC)))

Astrato.io Head of R&D

View solution in original post

9 Replies
MK_QSL
MVP
MVP

Use

=Time(Avg([End Work Shift]),'hh:mm:ss')

Emmanuelle__Bustos
Partner - Specialist
Partner - Specialist
Author

Hi Manish

Thanks for your response, but this formula only gives time format but the result is the same. The problem is not the format but the final result when select dates with not subsequent days.

rbecher
MVP
MVP

Hi Emmanulle,

could you upload an example of the issue. I cannot recreate the issue with the Excel data. Interesting, QlikView calculates an average of 9:01:41 for the second scenario...

- Ralf

Astrato.io Head of R&D
Emmanuelle__Bustos
Partner - Specialist
Partner - Specialist
Author

Is already attached.

rbecher
MVP
MVP

The solution is:

=time(avg(frac(HORA_SALIDA_TIME)))

Since the field contains a whole timestamp value..

Astrato.io Head of R&D
rbecher
MVP
MVP

..or the other field:

=time(avg(frac(HORA_SALIDA_HHC)))

Astrato.io Head of R&D
Emmanuelle__Bustos
Partner - Specialist
Partner - Specialist
Author

Hey Ralf Becher

It Works!!  But stil don't get how the Frac() function does the trick since I know it returns the fraction part, so this means that the integer part is the other timestamp info?

If this is the case my doubt is:

In the script I load the field HORA_SALIDA as HORA_SALIDA (it is loaded as numeric format like 41792.377....)

so I load                    Time(HORA_SALIDA) as HORA_SALIDA_TIME (it is loaded as 9:04:12 AM ....)

and load         l TimeStamp(HORA_SALIDA) as HORA_SALIDA_HHC (it is loaded as 06/06/2014 9:04:12 AM...)

since in the second load I used the Time() function for loading then I wasn't supposed to need the Frac() function in the Expression was I? If so is because even when using the Time() function the fields still load the integer part corresponding to the Full TimeStamp??

For now this is Solved thank you so much for your help and if you are able to clarify this last point I will be twice thankful.

rbecher
MVP
MVP

Right, the fraction is the time part. One day = 1. You could also solve the problem during load using the Time#() function - Time() does only the formatting of the text representaion, doesn't touch the value; or Frac() itself. It's a bit confusing here that you see the text representaion of time only here, but the value is a full timestamp. It has cost me some time to find it out although it is a very simple problem..

Astrato.io Head of R&D
Emmanuelle__Bustos
Partner - Specialist
Partner - Specialist
Author

Yes I already get it Ralf,

The Time#() function was the necessary since the beginning. Once again thank you for your help!!!

Best Regards!