topic Re: SET Analysis: Selecting values in an expression based on expression in another field in QlikView

SET Analysis: Selecting values in an expression based on expression in another field

Wed, 25 Nov 2020 16:16:04 GMT

I have a straight table with

3 Dimensions:

InitiativeName (text)
ProgramName (text)

ProgramRAG (text) - Either Red, Amber or Green

and 6 Expressions which give a count of the following :

# of Red Projects

# of Amber Projects

# of Green Projects

# of Red Tasks

# of Amber Tasks

# of Green Tasks

I want my straight table to return records ONLY where the following is true:

# of Red Projects > 0 or

# of Red Tasks > 0 or

# of Amber Tasks > 0

In other words, if a row does not have any amber or reds in the expression counts, I want it to be suppressed.

I've been trying by using set analysis to no avail:

=Count({1<GreenProjectCount=P({1<TaskIsRed={">$(=0)"}>}GreenProjectCount)>}ProjectIsGreen)

In this example I tried just showing expressions for the count of green projects only on records that had red tasks. I successfully showed only those records w/ red tasks, but my green program count comes up blank.

Been stuck on this for hours so any help would be greatly appreciated!!

Re: SET Analysis: Selecting values in an expression based on expression in another field

sunny_talwar — Mon, 16 May 2016 20:17:08 GMT

Why don't you use an if statement for all your expressions

If([# of Red Projects] > 0 or [# of Red Tasks] > 0 or [# of Amber Tasks] > 0, YourExpression1)

If([# of Red Projects] > 0 or [# of Red Tasks] > 0 or [# of Amber Tasks] > 0, YourExpression2)

...

and this will make all the expressions null when the condition isn't met and hence will show only those rows where the condition is met?

Re: SET Analysis: Selecting values in an expression based on expression in another field

Mon, 16 May 2016 20:33:51 GMT

Hey Sunny,

I tried something similar to this:

=if((ProgramOverallStatus='Green') and

(Count(ProjectIsRed) = 0) and

(Count(ProjectIsAmber) = 0) and

(Count(TaskIsRed) = 0) and

(Count(TaskIsAmber) = 0),0,1)

I think I failed to mention that I'm trying to suppress ones that have a Program RAG of "Green" AND all of the counts listed above are = 0

This actually works perfectly and it returned a "1" for all of the items I want to keep and a "0" for the ones I'd like to remove. The only problem is, I can't suppress the "0" values for some reason...

I just tried the code you provided above:

=If(([RED PRJ] = 0) and ([AMBER PRJ] = 0) and ([RED MILESTONE] = 0) and ([AMBER MILESTONE] = 0), [GREEN PRJ], Null())

and I'm getting an "errors in expression" message.

Thanks for the quick response by the way.

-Julian

Re: SET Analysis: Selecting values in an expression based on expression in another field

sunny_talwar — Mon, 16 May 2016 20:36:52 GMT

How about using your current expression like this:

=If((ProgramOverallStatus='Green') and

(Count(ProjectIsRed) = 0) and

(Count(ProjectIsAmber) = 0) and

(Count(TaskIsRed) = 0) and

(Count(TaskIsAmber) = 0), Null(), 1)

Re: SET Analysis: Selecting values in an expression based on expression in another field

Mon, 16 May 2016 20:40:24 GMT

That worked.. Now how do I suppress the nulls in the expression? I went to presentation and put suppress zero values and suppress missing but those records still appear..

Re: SET Analysis: Selecting values in an expression based on expression in another field

sunny_talwar — Mon, 16 May 2016 20:45:51 GMT

Are you sure that you have added this if statement to all your expressions (no hidden expressions left)? Because this should have removed all the rows with null values across.

Re: SET Analysis: Selecting values in an expression based on expression in another field

Mon, 16 May 2016 20:54:18 GMT

I didn't realize I needed to add this to each of my expressions..

So if my Expression for # of red projects is:

Count(DISTINCT ProjectIsRed)

how do I combine that with your code?

Re: SET Analysis: Selecting values in an expression based on expression in another field

sunny_talwar — Mon, 16 May 2016 21:19:03 GMT

Something like this;

If(allconditionsmet, yourexpression, null())

Respoding from phone, hence not putting the whole thing

Re: SET Analysis: Selecting values in an expression based on expression in another field

Mon, 16 May 2016 21:20:34 GMT

Yes, that worked.. thanks!

The only thing is my counts look a little off but I think that's because of the underlying fields/data.. I'll have to verify but I think it's good.

Re: SET Analysis: Selecting values in an expression based on expression in another field

sunny_talwar — Mon, 16 May 2016 21:55:34 GMT

Count meaning the totals? You can fix those using sum(aggr(...)):

Sum(Aggr(theNewExpressionUsingtheIfStatement, YourTableDimension))