Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Problem with weighting scores on a Scorecard

Hi All,

Once again I need to turn to my Qlikview colleagues for a nudge in the right direction. As usual all help very gratefully received.

My problem is that I want to look at the amount of work done by users & weight it by the  average amount of time taken for the job by all users.

Consider the following data - for simplicity I have two users Joe & Fred who do two different jobs A & B

during the day they performed the following & I want to scorecard them to see who is working most efficiently.

personjob typetime taken
JoeA0.5
JoeA0.6
JoeA0.7
JoeB0.8
JoeB0.5
FredB0.9
FredA0.8

In Excel I can display the summary data as follows.

Note for column e  I say that for type A the average is 0.5+0.6+0.7+0.8 /4 = 0.65 & for type B the average is 0.5+0.8+0.9/3 = 0.73

and then apply it to the quantites to work out a deviation from the average

abcdefg
persontypetotal time takenno of jobsaverage by typed*eefficiency score = f-c
FredA0.810.650.65-0.15
FredB0.910.730.73-0.17
JoeA1.830.651.950.15
JoeB1.320.731.460.16

I have nearly got a formulae to work in Qlik - but it will only work for one user !!!!

my column e equivalent in Qlik displays 0.65 for Joe "A" & 0.73 for Joe "B" but "-" both times for Fred

Person & Type in my chart are Dimensions (in that order) & the expression average score is

(aggr(Sum(time taken),Type)/aggr(Count(Case),Type))*Count(Case)

1 Solution

Accepted Solutions
Not applicable

Re: Problem with weighting scores on a Scorecard

Hi,

You could also try this expressions,

For column E

aggr(nodistinct avg([time taken]), [job type])

For column F

count([job type])*aggr(nodistinct avg([time taken]), [job type])

For column G

count([job type])*aggr(nodistinct avg([time taken]), [job type]) - sum([time taken])

Regards,

Janzen

6 Replies
MVP
MVP

Re: Problem with weighting scores on a Scorecard

Try a NODISTINCT qualifier with the aggr() function:

=(aggr(NODISTINCT Sum(time taken) / Count(Case),Type))*Count(Case)

edit: It's probably even easier to use

=sum(TOTAL<[job type]> [time taken]) / count(TOTAL<[job type]> Case) * count(Case)

Not applicable

Re: Problem with weighting scores on a Scorecard

Hi,

You could also try this expressions,

For column E

aggr(nodistinct avg([time taken]), [job type])

For column F

count([job type])*aggr(nodistinct avg([time taken]), [job type])

For column G

count([job type])*aggr(nodistinct avg([time taken]), [job type]) - sum([time taken])

Regards,

Janzen

Not applicable

Re: Problem with weighting scores on a Scorecard

Swuehl - your answer indicated I was on the right track but did not fix the problem.

Janzen - Spot on.  However once again shows that as good as qlikview is (& I think its really good) sometimes

the syntax can get very complex !!

MVP
MVP

Re: Problem with weighting scores on a Scorecard

Right, the syntax can get very complex, the learning curve might get a little steep, so good luck and keep on going. But this is due to the fact that QV's syntax is really powerful (but unfortunately also due to the fact that it is not always as consistent and easy as possible).

May I ask why my above expression haven't fixed the issue? I do get the numbers from your result table, column f when applying the expression to your sample data (well, you haven't provided Case field, so I used actually just person).

Using avg() for the average is of course a good idea, so I would do it like this without any advanced aggregation:

=avg(TOTAL<[job type]> [time taken]) * count(person) - sum([time taken])

Not applicable

Re: Problem with weighting scores on a Scorecard

I have attached my original test qvw for you to look at. when i added your code it made no change for me.

However for a bonus 5 points the one extra thing I would like to do is to produce a subtotal by person of their net deviation from the average.so using the code above Fred would have a total displayed of -0.32 & joe would show 0.31.

Janzens reply works perfectly for each line but I cannot get it to subtotal.

MVP
MVP

Re: Problem with weighting scores on a Scorecard

I am just curious why my expressions seemed to not be working while others (which look to me essentially the same) seem to work.

After copying my expressions into your sample file, I am still unsure, the results look ok to me.

Besides this, I think using the solution with no advanced aggregation, i.e. using the TOTAL qualifier, should solve your subtotal issue.

Have a nice evening,

Stefan

Community Browser