3 Replies Latest reply: Jan 13, 2011 2:28 PM by Karl Pover RSS

    Show average in chart

    Anuradha.Arun

      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

        • Show average in chart
          Karl Pover

          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.

            • Show average in chart
              Anuradha.Arun

              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

                • Show average in chart
                  Karl Pover

                  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.