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

# Count of times expression is within a range

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

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

I am sorry. I meant varX.

varX = vScore

• ###### Re: Count of times expression is within a range

I still don't completely understand how your data is laid out. Would you be able to share screenshots, if not a sample?

• ###### Re: Count of times expression is within a range

Hi,

The top row are week numbers.  To the left of each row of numbers would be a person's name.   So "Person A" (first row) has the scores of 84.77%. 87.34%, 88.46%.   My expression calculates those values in the pivot.  For each column I want to calculate the % of blue, red, yellow and green cells for that column (Week).

• ###### Re: Count of times expression is within a range

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

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

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

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

*,

(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

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

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

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

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

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

 Name Week Satisfied Survey Total Survey Sham 1 98 98 Sham 2 71 90 Mark 1 89 94 Mark 2 77 96 Latrice 1 91 97 Latrice 2 94 94 Carmen 1 84 89 Carmen 2 98 100

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

though this is not very clean you can use a valuelist to apply your ranges and then based on that display only the ones you want

I had to create a composite key of name and week to obtain a unique value per week

• ###### Re: Count of times expression is within a range

Hi Ramon,

I'm not sure you understand my intent.  I don't have a visibility issue. I need the percent of employees for that week that fit into these performance buckets. A report that looks like this from the table created with my varX.

Green:  22%

Blue:   60%

Yellow: 20%

Red: 10%

• ###### Re: Count of times expression is within a range

so when you say that week are you referring to an specific selected week or just in general the ones that are being displayed on the varX Report ?

I am assuming something like this, correct ?

 Rate Week 1 2 Blue 25.00% 50.00% Green 75.00% 0.00% Yellow 0.00% 0.00% Red 0.00% 50.00%

you can create this report by just tweaking  the document I previously uploaded, my assumption is that the % is by the number of users that worked that week, and for this to work you still need to create the composite key between name and week, you might be able to get it to work without creating a field by using an aggr, but that would be to much of a hassle in my opinion

• ###### Re: Count of times expression is within a range

Hi Yes!  In week 1  25% of my people were within the range .90-100.  It just appears impossible to get this count info off a table populated on a variable based on aggregation formulas.  My intent is to graph that table as a stacked bar by week.  But the first step of course is counting!

• ###### Re: Count of times expression is within a range

It appears I cannot use a algebraic substitution!   I have to use the base formula for it to work. Not my variable. Nesting appears to be the problem.  Possibly due to the order in which is calculates and the layered aggregations?!

Works:

Green = sum(aggr(if(sum(Yes)/sum([Surveys])>=.90,1,0),ldap))

Does Not Work:

Green = sum(aggr(if(varX])>=.90,1,0),ldap))

• ###### Re: Count of times expression is within a range

Many thanks for this. I spent a day trying to get this working to no avail. Your solution was exactly what I needed

• ###### Re: Count of times expression is within a range

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.

 Name Week Satisfied Survey Total Survey Sham 1 98 98 Sham 2 71 90 Mark 1 89 94 Mark 2 77 96 Latrice 1 91 97 Latrice 2 94 94 Carmen 1 84 89 Carmen 2 98 100

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