6 Replies Latest reply: Oct 9, 2012 12:34 PM by Stefan Wühl RSS

    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)

       

       

       

       

        • Re: Problem with weighting scores on a Scorecard
          Stefan Wühl

          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)

           

           

          • 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

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

                  • Re: Problem with weighting scores on a Scorecard
                    Stefan Wühl

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

                      • 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.

                          • Re: Problem with weighting scores on a Scorecard
                            Stefan Wühl

                            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