Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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!

20 Replies
ramoncova06
Specialist III
Specialist III

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

Anonymous
Not applicable
Author

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        

ramoncova06
Specialist III
Specialist III

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)

Anonymous
Not applicable
Author

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


ramoncova06
Specialist III
Specialist III

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

Anonymous
Not applicable
Author

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%

Anonymous
Not applicable
Author

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


ramoncova06
Specialist III
Specialist III

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 12
Blue25.00%50.00%
Green75.00%0.00%
Yellow0.00%0.00%
Red0.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

Anonymous
Not applicable
Author

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!

Anonymous
Not applicable
Author

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