topic Re: Count of times expression is within a range in QlikView

Count of times expression is within a range

Anonymous — Thu, 12 May 2016 22:23:23 GMT

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)

Name	Week #
Person A	varX=.75
Person B	varX=.8
Person C	varX=.82
Person D	varX=.73
Person E	varX=.91
Person E	varX=.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 — Thu, 12 May 2016 22:37:16 GMT

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

Anonymous — Thu, 12 May 2016 22:40:48 GMT

I am sorry. I meant varX.

varX = vScore

Re: Count of times expression is within a range

Anonymous — Fri, 13 May 2016 00:24:58 GMT

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

sunny_talwar — Fri, 13 May 2016 13:48:29 GMT

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

Anonymous — Fri, 13 May 2016 15:26:54 GMT

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

ramoncova06 — Fri, 13 May 2016 17:16:42 GMT

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

Anonymous — Fri, 13 May 2016 17:47:23 GMT

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

ramoncova06 — Fri, 13 May 2016 18:36:00 GMT

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

Anonymous — Fri, 13 May 2016 18:57:49 GMT

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

ramoncova06 — Mon, 16 May 2016 13:44:40 GMT

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

Anonymous — Wed, 18 May 2016 15:27:37 GMT

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

ramoncova06 — Wed, 18 May 2016 17:49:24 GMT

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

Anonymous — Wed, 18 May 2016 18:57:57 GMT

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

ramoncova06 — Fri, 20 May 2016 13:36:24 GMT

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

Anonymous — Fri, 20 May 2016 16:05:25 GMT

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

Anonymous — Fri, 20 May 2016 16:09:08 GMT

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

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

ramoncova06 — Fri, 20 May 2016 16:41:26 GMT

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

Anonymous — Mon, 23 May 2016 16:12:02 GMT

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

Anonymous — Fri, 27 May 2016 21:12:10 GMT

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