8 Replies Latest reply: May 30, 2012 1:05 PM by Stefan Wühl RSS

    Lookup

      I am trying to do a lookup on a specific performance related metric. Each customer service rep receives an average of 5 call scores each month on a 0-100 range. After that, the agent also receives a leveling score on a 1-5 range based on the monthly average of the call score. I am trying to get my script so that whenever I select a range of months, the script does a loookup from an excel spreadsheet. Currently when I select a range of months I am getting an average of the leveling scores rather than receiving a leveling value based on the average of the call scores. I hope my explanation makes sense.

       

       

      Here is my script:

       

       

      Quality:

      Directory;

      LOAD

      '' AS Date,

      Year & '-' & LEFT(Month,3) AS YearMonth,

      Associate AS Rep_Name,

      Associate & Year & '-' & LEFT(Month,3) AS RepYearMonth,

      (Score)

       

       

      FROM \\swqlikv1\QLIKVIEW\1_Resources\ContactCenter\CallScoreAssignments.xls (biff, embedded labels, table is [Eval Assignments$])

      WHERE Year >= 2010;

       

       

      Quality1:

      Load

      YearMonth,

      Rep_Name,

      RepYearMonth,

      Round(Avg(DISTINCT(Score)),-.1) As Quality

       

       

      Resident Quality

      Group By  Rep_Name, YearMonth, RepYearMonth;

      Drop Table Quality;

       

       

      Left Join (Quality1)

      LOAD

       

      Round((Quality),-.1) As Quality,

      [Quality Level] AS Quality_Level


       

       

      FROM

      Leveling.xlsx

      (ooxml, embedded labels, table is Sheet1);

       

       

       

      Please let me know if you need any further information.

       

      Thank you,

       


        • Lookup
          Stefan Wühl

          Saurav S,

           

          I am not sure if I fully understood what your want to achieve. Could you post some sample lines of data together with your expected result?

           

          Lookup() is a script function, so I believe you can't call this function when you make any selections in the front end.

          If you are not talking about the QV lookup function, please clarify your setting and requirement.

           

          Regards,

          Stefan

            • Re: Lookup

              Hi Stefan,

               

              I am not certain whether I need to use the lookup function or not. I am sorry if the title of my post was misleading.

               

              I have attached a table of level scores which shows the ranges of the level score, which are correlated with the average quality.

               

              Example:

               

              Jan:

              Quality score= 100

              Level Score= 5

               

              Feb:

              Quality Score =88.50

              Level Score = 4.50

               

              Mar:

              Quality Score=83.10

              Level Score=.05

               

              I would like to be able to get an average of the two and three month quality score and look up that value in the attached table. So in this example, the 3 month quality score would be (100+88.50+83.10)/3= 92.5 and the leveling score would be a 5.  This is the type of logic I am trying to incorporate in my script.

               

              Please see the attached table for further details regarding the call score and the level score ranges.

                • Re: Lookup
                  Stefan Wühl

                  Saurav S,

                   

                  guess you can get your result with different approaches, best is depending the context (where you want to display the result).

                   

                  I've loaded your table and created some Scores for some Months.

                   

                  In global context, you can get your Quality Level by using something like

                   

                  =only({<Quality= {$(=num(floor(avg(Score),0.1),'#.00'))}>} [Quality Level])

                   

                  Here I use a set expression to filter on the Quality with value num(floor(avg(Score),0.1),'#.00')

                  This looks a bit strange, the num(floor()) is just to match your number format.

                   

                  In a table, you can achieve similar using

                  =aggr(if(floor(avg(Score),0.1)= Quality,[Quality Level]),Month,Quality)

                   

                  See attached for a sample file.

                   

                  Regards,

                  Stefan

                    • Re: Lookup

                      Stefan,

                       

                      The number in the text box is exactly what I want but I do not want it to display in a textbox. Is it acceptable to write this in the script? If so, where?

                       

                      =only({<Quality= {$(=num(floor(avg(Score),0.1),'#.00'))}>} [Quality Level])

                       

                      I have uploaded the script with the actual call scores so this may help. I also changed the month format to Year-Month, because that is the format I need the data.

                       

                      Thank you for the all of your help thus far.

                       

                      Saurav


                        • Re: Lookup
                          Stefan Wühl

                          No, you can't use a set expression in the script (since the data model is not completely built at this time, selecting on field values will not work).

                           

                          If you don't want to show it in a text box, where do you want to show it?

                           

                          You could create a variable in Settings->Variable Overview and name it e.g. vTest with definition

                          =only({<Quality= {$(=num(floor(avg(Score),0.1),'#.00'))}>} [Quality Level])

                           

                          Then use this variable anywhere in your objects (it will just hold the value as calculated and shown in the text object):

                          =vTest

                           

                          See also attached.

                           

                          With regard to your Year-Month, I would suggest using Monthname() function instead, which will still hold the month start date in the numerical representation or maybe

                          Date(monthstart(Date),'YYYY-MMM') as YearMonth,

                           

                          if you need a different formatting.

                           

                          Hope this helps,

                          Stefan

                            • Re: Lookup

                              Hi Stefan,

                               

                              I think your suggestion works fine for this situation, however, I have a multiple metrics that I need to apply similar logic to. I thought there would be a much simpler solution to this, since I am already getting the monthly levels scores that I want. My logic breaks when I try to get a leveling score for several months. Instead of looking at the ranges for the quality leveling score I am getting an average of the quality leveling scores, which is slightly different.

                               

                              I have uploaded my script to show you how I am currently scripting this. I will need to apply a similar logic to other metrics as well.

                               

                              Thanks.

                                • Re: Lookup

                                  Can you please explain this formula.

                                   

                                  =only({<Quality= {$(=num(floor(avg(Score),0.1),'#.00'))}>} [Quality Level])

                                   

                                  Thank You!

                                  • Re: Lookup
                                    Stefan Wühl

                                    Saurav, S

                                     

                                    I noticed that your last load

                                     

                                    Left Join (Quality1)

                                    LOAD

                                     

                                    Round(Quality,-.1) As Quality1,

                                    [Quality Level] AS Quality_Level1

                                     

                                    FROM

                                    [Y:\PL Operations\Leaders\Analyst\SSharma\Capstone Project\Leveling.xlsx]

                                    (ooxml, embedded labels, table is Sheet1);

                                     

                                    will create a full Quality / QualityLevel table per existing record (beacuse you renamed the fields and there are no matching fields in the joining table Quality1). Is this what you intend to do?

                                     

                                    Looking at your problem, you only have problems with the total line in your table, right?

                                     

                                    You could use a variable as suggested above and then use

                                     

                                    =if(dimensionality()=0,vTest,avg(DISTINCT([Quality Level])))

                                     

                                    to use your variable only for the total line.

                                     

                                    If your quality / quality level is not changing day to day (i.e. your table / logic will not be changed by the users in the excel), you could also get rid of the lookup and calculated the quality level on the fly using a dollar sign expansion with arguments:

                                     

                                    Define a variable vQL (no equal sign here):

                                    if($1>=89,5,if($1>=85,($1-85)+1,if($1>=83,($1-83)/2,0)))

                                     

                                    and then in your expression use:

                                    =$(vQL(avg(Quality)))

                                     

                                    See attached for both approaches.

                                     

                                    Regards,

                                    Stefan

                                     

                                    P.S: Regarding your last post, what do you think this expression does?