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: 
timpoismans
Specialist
Specialist

Pivot table total as measurement

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

1 Solution

Accepted Solutions
sunny_talwar

May be this

(

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

+

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

+

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

+

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

)

/4

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

View solution in original post

4 Replies
sunny_talwar

May be this

(

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

+

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

+

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

+

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

)

/4

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

timpoismans
Specialist
Specialist
Author

Should've thought of that.

But I don't see how that makes the difference for just that one employee?

sunny_talwar

I am guessing that this employee might have a different Max WeightFactor compared to others.... you can check this by adding a new expression Max(WeightFactor) and see if that is true. Add different set analysis to check all of them if you wish to confirm

timpoismans
Specialist
Specialist
Author

Just checked, he indeed does have one different weight factor for one of the factors.

Thank you very much again!