Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Emmanuelle-Bustos
Valued Contributor

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
MVP
MVP

Re: Avg(TimeValues) with not subsequent days

..or the other field:

=time(avg(frac(HORA_SALIDA_HHC)))

9 Replies
MVP
MVP

Re: Avg(TimeValues) with not subsequent days

Use

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

Emmanuelle-Bustos
Valued Contributor

Re: Avg(TimeValues) with not subsequent days

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.

MVP
MVP

Re: Avg(TimeValues) with not subsequent days

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

Emmanuelle-Bustos
Valued Contributor

Re: Avg(TimeValues) with not subsequent days

Is already attached.

MVP
MVP

Re: Avg(TimeValues) with not subsequent days

The solution is:

=time(avg(frac(HORA_SALIDA_TIME)))

Since the field contains a whole timestamp value..

MVP
MVP

Re: Avg(TimeValues) with not subsequent days

..or the other field:

=time(avg(frac(HORA_SALIDA_HHC)))

Emmanuelle-Bustos
Valued Contributor

Re: Avg(TimeValues) with not subsequent days

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.

MVP
MVP

Re: Avg(TimeValues) with not subsequent days

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..

Emmanuelle-Bustos
Valued Contributor

Re: Avg(TimeValues) with not subsequent days

Yes I already get it Ralf,

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

Best Regards!

Community Browser