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: 
Not applicable

Show average in chart

Dear Experts, I am a newbie to Qlikview and needed help with a case we have.

We are tracking access cards transactions of employees. I have data for employees - Name, IN Date & Time and OUT Date and Time. I have to show First IN and Last OUT Time per employee and Date in a chart. The parameters given for user selection are Names of employees and dates. I have made a chart with Dimension as a Hierarchical group of fields Name and Date. The expressions has 2 fields - min(INTime) and max(OUTTime).

1. When I select 20 Dec , the chart shows Andy - 7:30 AM, Greg 8:00 AM - correct. Similarly when I select 21 Dec , chart shows Andy 8:00 AM and Greg 8:30 AM - correct

2. When I select 20 Dec and 21 Dec and Andy, the chart shows 7:30 AM and 8:00 AM - correct. Similarly when I select 20 Dec and 21 Dec and Greg, chart shows 8:00AM and 8:30 AM - correct

3. When I select 20 and 21 Dec and do not select any name - it shows Andy - 7:30 AM and Greg - 8:00 AM. This is the min(INTime) for Andy and Greg calculated over the dates selected. But the user wants to see the average - Andy - 7:45 AM and Greg 8:15 AM

4. Please note I do not have a field called MIN(INTime) or Max(OUTTime). I just have lots of INTime per day per employee and lots of OUTTime per day per employee.

Date Name MIN(INTime)

20 Dec Andy 7:30 AM

20 Dec Greg 8:00 AM

21 Dec Andy 8:00 AM

21 Dec Greg 8:30 AM

Kindly help. Thanks

3 Replies
pover
Luminary Alumni
Luminary Alumni

Use the aggr() function in the following way:

avg(aggr(min(INTime),Date))

This will give you the minimum INTime of each day and average it.

Regards.

Not applicable
Author

Thanks for your quick reply. But I am not getting to the required solution.

I think there is a problem with the average calculation on dates. The INTime and Date fields are derived fields as follows. MessageUTC field is the actual field in the transaction table which is in the GMT format - I am adding 4 hours to get local time.


if (WILDMATCH( XmlMessage, '*INDIRECTION*', '*OUTDIRECTION*') =1,Hour(MessageUTC + 1/6)&':'&Minute(MessageUTC + 1/6)) AS INTime,

if (WILDMATCH( XmlMessage, '*INDIRECTION*', '*OUTDIRECTION*') =2,Hour(MessageUTC + 1/6)&':'&Minute(MessageUTC + 1/6)) AS OUTTime,

Day(MessageUTC + 1/6)&'-'&Month(MessageUTC + 1/6)&'-'&Year(MessageUTC + 1/6) as Date,

Kindly help. Thanks

pover
Luminary Alumni
Luminary Alumni

Those fields will be strings, but that is strange since you posted above that you were using min(INTime) and if INTime was a string it would return null. So, I must be missing some other detail, but in the script you should use time#() and date#() functions. For example,

time#(

if (WILDMATCH( XmlMessage, '*INDIRECTION*', '*OUTDIRECTION*') =1,Hour(MessageUTC + 1/6)&':'&Minute(MessageUTC + 1/6))

, 'hh:mm') AS INTime,

time#(

if (WILDMATCH( XmlMessage, '*INDIRECTION*', '*OUTDIRECTION*') =2,Hour(MessageUTC + 1/6)&':'&Minute(MessageUTC + 1/6))

, 'hh:mm')

AS OUTTime,

date#(

Day(MessageUTC + 1/6)&'-'&Month(MessageUTC + 1/6)&'-'&Year(MessageUTC + 1/6)

,'DD-MMM-YYYY')

as Date,

Let's see if that helps you with the average function.

Regards.