20 Replies Latest reply: Jun 21, 2016 9:17 AM by Eoin Darcy RSS

    Count of times expression is within a range

    Brian Joseph

      Hi,

       

      I have  a pivot table similar to below.  Each person is given a score every week and I have the expression varX that calculates it.  I show it below for clarity.   I want to get a % of the scores within a performance range for each week. I will be working with four UNEVEN ranges. I tried the set analysis format below but it does not work. It returns a number but if I change the number the returned values don't change. I suspect its counting everything.  Thank you in advance for any ideas.

       

      Returns a number but changing ranges has no effect:

      vTier2=Count({$<vScore={">.85<=.90"}>}Name) 

       

      NameWeek #
      Person AvarX=.75
      Person BvarX=.8
      Person CvarX=.82
      Person DvarX=.73
      Person EvarX=.91
      Person EvarX=.84

       

      p.s. I am new to QV and find the syntax confusing with the use of single vs. double quotes and where to place symbols. So if you can explain any incorrect syntax I would appreciate it!

        • Re: Count of times expression is within a range
          Sunny Talwar

          What is vScore here? Is this a field name or is this a variable? Would you be able to post a sample qvw? For set analysis syntax you can look at this guide

           

          Set Analysis: syntaxes, examples

            • Re: Count of times expression is within a range
              Brian Joseph

              I am sorry. I meant varX.  

               

              varX = vScore

              • Re: Count of times expression is within a range
                Brian Joseph

                Hi Sunny,

                 

                Strangely I know I replied to this but don't see it.  My error. I changed the name.

                 

                varX = VScore

                  • Re: Count of times expression is within a range
                    Ramon Covarrubias

                    you are not able to use the vScore variable that way, normally the variables that work with set analysis tend to be a fixed value and you compare a field against them

                     

                    is "varX" a calculated value or a fixed value  ?  If it is a fixed value just use that instead of the variable and it should work, if it is a calculated, can you run that calculation in the script ?

                      • Re: Count of times expression is within a range
                        Brian Joseph

                        Hi Ramon,

                         

                        I thought that it couldn't be used that way either but I was told otherwise, so I posted.  Each numbers you see in the chart is the evaluation of the expression varX which is an approval per person per week.

                         

                        varX =  approved/ (approved + disapproved)

                         

                        I want the percentage of people who fall within an approval rating range each week (column).  I've only been at this for a few weeks. I only know how to load and do some cleaning with scripts so unfortunately I couldn't tell you at this point.

                          • Re: Count of times expression is within a range
                            Ramon Covarrubias

                            see if this expression works, I did not test this yet, but by comparing to other expressions I have used, it should work

                             

                            Count({$<Name={"=(approved/ (approved + disapproved)) >.85<=.90"}>}Name)

                             

                             

                            another option (and the one I recommend) is to create a field in the script, something like this should work

                             

                            Load

                            *,

                            (approved/ (approved + disapproved))  as WeeklyRate

                            from ......


                            and then you could replace the variable with this field

                              • Re: Count of times expression is within a range
                                Brian Joseph

                                Thanks. I will try it but I don't understand how that would work as it seems this is trying to equate names such as  "Tom", "Lisa", to a numeric value range.  I will substitute my variable in below as that is the equivalent.

                                 

                                Count({$<Name={"=varX >.85<=.90"}>}Name)

                                  • Re: Count of times expression is within a range
                                    Ramon Covarrubias

                                    what you are doing is running an advanced search in the set analysis this provides not necessarily mapped to the type of data that you are looking for

                                     

                                    Why is it called Set Analysis?

                                     

                                    this explains what is set analysis

                                      • Re: Count of times expression is within a range
                                        Brian Joseph

                                        Thanks Ramon but this doesn't work for my scenario.  I've played with set analysis before but unlike the others and the example link you gave, I am using an expression not a dimension.  I am believing that Qlikview doesn't have the capability to substitute a variable composed of a  Sum(x)/Sum(y) .  I believe if this was a table with hard coded values loaded into it then this would likely work.

                                         

                                        If possible I need a workaround where maybe a temp table (such as below)  can be created from a script. But if there is a year or two of data, this would be ugly for the 700 people I would have to generate it for per week!  Is it possible to have a script do this but within a restricted time period, say a month?

                                         

                                        Person  Week     Score

                                        Linda         2         sum(approved)/sum(disapproved)

                                        Linda         3         ( )

                                        John          2         ( )

                                        John        

                                          • Re: Count of times expression is within a range
                                            Ramon Covarrubias

                                            Yes you can do the sum by restricting the month you want to sum though I would have to see some sample of your data to recommend a solution.

                                             

                                            let me see if a can work on a POC of the expression

                                             

                                            Count({$<Name={"=(approved/ (approved + disapproved)) >.85<=.90"}>}Name)

                                              • Re: Count of times expression is within a range
                                                Brian Joseph

                                                Here is an example of raw data.  You need a variable comprised of an aggregated expression to replicate my scenario.

                                                  

                                                NameWeekSatisfied SurveyTotal Survey
                                                Sham19898
                                                Sham27190
                                                Mark18994
                                                Mark27796
                                                Latrice19197
                                                Latrice29494
                                                Carmen18489
                                                Carmen298100

                                                 

                                                From the table above I would create the pivot below.  Looking below, the varX has created the scores for the employees (screenshot in post above).  I need to look down the week columns and count the number of people who fit within one of four unevenly distributed score ranges.  So the set analysis criteria would have to be on varX not a dimension like every example I've seen. It may not be possible.


                                                Pivot:    Dimension: Week    Expression: varX

                                                varX = Sum ([satisfied survey])/sum([Total Survey])

                                                                    

                                                                              Score          Score           

                                                Name        Week        1                2

                                                Sham                      100%            78.89%

                                                Mark                        varX              varX

                                                Latrice                     varX              varX

                                                Carmen                   varX              varX


                              • Re: Count of times expression is within a range
                                Brian Joseph

                                Hi everyone,

                                 

                                I was not replying to the original discussion but only to one individual by mistake as I am new.

                                 

                                 

                                Here is an example of raw data.  You need a variable comprised of an aggregated expression to replicate my scenario.

                                 

                                NameWeekSatisfied SurveyTotal Survey
                                Sham19898
                                Sham27190
                                Mark18994
                                Mark27796
                                Latrice19197
                                Latrice29494
                                Carmen18489
                                Carmen298100

                                 

                                From the table above I would create the pivot below.  Looking below, the varX has created the scores for the employees (screenshot in post above).  I need to look down the week columns and count the number of people who fit within one of four unevenly distributed score ranges.  So the set analysis criteria would have to be on varX not a dimension like every example I've seen. It may not be possible.


                                Pivot:    Dimension: Week    Expression: varX

                                varX = Sum ([satisfied survey])/sum([Total Survey])

                                                   

                                                              Score          Score          

                                Name        Week        1                2

                                Sham                      100%            78.89%

                                Mark                        varX              varX

                                Latrice                     varX              varX

                                Carmen                   varX              varX