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

[#1]

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

[sunny_talwar]

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?

[#3]

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

[sunny_talwar]

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)

[#5]

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

[sunny_talwar]

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.

[#7]

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?

[sunny_talwar · Accepted solution]

Something like this;

If(allconditionsmet, yourexpression, null())

Respoding from phone, hence not putting the whole thing

[#9]

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.

[sunny_talwar]

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

Sum(Aggr(theNewExpressionUsingtheIfStatement, YourTableDimension))