4 Replies Latest reply: Nov 20, 2017 7:28 AM by Tim Poismans RSS

    Pivot table total as measurement

    Tim Poismans

      Hello

       

      In an application for a customer, we're calculating a score based on a number of factors that represents the overall driving behaviour.

       

      Now we want to compare the employee's individual score vs the average score.

       

      Currently, I'm using the following formula:

       

      (

      (Sum(total<Year,Month,Week>{<MessageType = {'Acceleration'}>}IndType)*(Max({<MessageType = {'Acceleration'}>}WeightFactor))/10)

      +

      (Sum(total<Year,Month,Week>{<MessageType = {'Deceleration'}>}IndType)*(Max({<MessageType = {'Deceleration'}>}WeightFactor))/10)

      +

      (Sum(total<Year,Month,Week>{<MessageType = {'Curve'}>}IndType)*(Max({<MessageType = {'Curve'}>}WeightFactor))/10)

      +

      (Sum(total<Year,Month,Week>IndSpeed)*(Max({<%MessageTypeKey = {200}>}WeightFactor))/10)

      )

      /4

      / ((Sum(total<Year,Month,Week>Distance)/1000)/50)

       

      The problem is, it gives me the average for that week, listing the weekly average in general next to each employee, so they can see the number theirs is being compared with, except that for one employee, the average is off. See screenshot:

      Total issue.PNG

       

      Anyone an idea on how this is possible or what may be the cause?

       

      Thanks in advance.

       

       

      Kind regards,

       

      Tim Poismans