Need help with an Expression calculation.

[#1]

I am having some trouble trying to figure out an expression. We are trying to calculate a KPI score that we can chart over time.

We are trying to do a calculation where we take

1 - ( Sum of the Average Score for every field for every project
/ Sum of the Average Worst Score for every field for every project) * 100
For every KPI for every Project for every Month/Year.

I've also attached an Excel which might help further explain the calculation we are trying to achieve.

I tried this expression and it doesn't seem to be working correctly. For the KPI Resource Management the Score for June 2009 should be 85.256757. The score for July 2009 should be 80.7801005.

avg(aggr(((1- (sum(aggr(Avg(QuestionnaireResponseField.Score), Project.Name, QuestionnaireResponseField.FieldId)) / sum(aggr(Avg(QuestionnaireResponseField.WorstScore), Project.Name, QuestionnaireResponseField.FieldId))))*100) , Project.Name,QuestionnaireCalendar.MonthYear, CartridgeKPIType.Name))

I have attached a .qvw file that has what we have done so far with the chart.

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/4705.QV-Expression-Explained.xlsx:550:0]

[Anonymous]

Hi castalj05,

I created a small example based on the Excel file where I've done the average calculations on the script side already which might make the expression easier to write and faster to execute since it's less for QlikView to calculate each time the selection changes.

Hope you have some use for it.

Edit: Couldn't figure out how to attach a file so here's the script:

T1:
LOAD * INLINE [
User, Field#, KPI, Score, WorstScore
1, 9, 2, 1, 4
2, 9, 2, 2, 4
3, 9, 2, 1, 4
1, 10, 2, 1, 3
2, 10, 2, 2, 3
];

T2:
Load
Field#,
avg(Score) as AvgScore,
avg(WorstScore) as AvgWorstScore
resident T1
group by Field#;

This in turn gives the KPI that you want:

=(1-Sum(distinct {1} AvgScore)/Sum(distinct {1} AvgWorstScore))*100