Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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
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)
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
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
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%
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
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
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!
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))