Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Problem with weighting scores on a Scorecard

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Not applicable

2012-10-08
03:32 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

person | job type | time taken |

Joe | A | 0.5 |

Joe | A | 0.6 |

Joe | A | 0.7 |

Joe | B | 0.8 |

Joe | B | 0.5 |

Fred | B | 0.9 |

Fred | A | 0.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

a | b | c | d | e | f | g | |

person | type | total time taken | no of jobs | average by type | d*e | efficiency score = f-c | |

Fred | A | 0.8 | 1 | 0.65 | 0.65 | -0.15 | |

Fred | B | 0.9 | 1 | 0.73 | 0.73 | -0.17 | |

Joe | A | 1.8 | 3 | 0.65 | 1.95 | 0.15 | |

Joe | B | 1.3 | 2 | 0.73 | 1.46 | 0.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,104 Views

1 Solution

Accepted Solutions

Not applicable

2012-10-09
02:58 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

607 Views

6 Replies

swuehl

MVP

2012-10-08
03:49 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

2012-10-09
02:58 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

608 Views

Not applicable

2012-10-09
05:04 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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 !!

607 Views

swuehl

MVP

2012-10-09
05:29 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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])*

607 Views

Not applicable

2012-10-09
09:24 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

607 Views

swuehl

MVP

2012-10-09
12:34 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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