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: 
tastydew
Contributor
Contributor

Get a count from nested if statement

I have the following nested if statement to count specific items in a table...

if(if([Status]='Completed',Count([Req #]))=1,Count([Req #]))

How can I get a count of the results it returned?

7 Replies
Vegar
MVP
MVP

Try to put sum() around your expression.
SUM( if(if([Status]='Completed',Count([Req #]))=1,Count([Req #])) )
tastydew
Contributor
Contributor
Author

I am unable to do this because it would be nested aggregation
Vegar
MVP
MVP

I see. SET analysis might be the way to go. Try something like this:

Count( {$<[Req #] = {“=Count({<Status={'Completed'}>}[Req #]) = 1”}>} [Req #] )
MynhardtBurger
Contributor III
Contributor III

I am correct in saying your existing measure...

if(
if(
[Status]='Completed',
Count([Req #])
) = 1,
Count([Req #])
)

be restated like so:

if(
Count({<[Status]={'Completed'}>} [Req #]) = 1,
Count([Req #])
)

Now you want to count the results which that returns.

So, like Vegar suggested, you should be able to get the count of the results by wrapping it in a sum:

Sum( Count({<[Status]={'Completed'}>} [Req #]) = 1 ) * -1

The numerical representation of TRUE() is '-1', and FALSE() is '0'. So you can sum the true values and multiply by -1 to get the count.

Vegar
MVP
MVP

I think you @MynhardtBurger will get trouble with the nested aggregations sum(count()) as @tastydew wrote earlier.
tastydew
Contributor
Contributor
Author

I still get the following:

 

Capture.PNG

MynhardtBurger
Contributor III
Contributor III

Sum(
	aggr(
	    count({<[Status]={'Completed'}>} [Req #]) = 1,
    	    [Req #]
	)
) * -1