Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

tastydew
New 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
Partner
Partner

Re: Get a count from nested if statement

Try to put sum() around your expression.
SUM( if(if([Status]='Completed',Count([Req #]))=1,Count([Req #])) )
Please ekskuse my Norglish and Swenglish typos.
tastydew
New Contributor

Re: Get a count from nested if statement

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

Re: Get a count from nested if statement

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

Count( {$<[Req #] = {“=Count({<Status={'Completed'}>}[Req #]) = 1”}>} [Req #] )
Please ekskuse my Norglish and Swenglish typos.
MynhardtBurger
New Contributor III

Re: Get a count from nested if statement

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.

Partner
Partner

Re: Get a count from nested if statement

I think you @MynhardtBurger will get trouble with the nested aggregations sum(count()) as @tastydew wrote earlier.
Please ekskuse my Norglish and Swenglish typos.
Highlighted
tastydew
New Contributor

Re: Get a count from nested if statement

I still get the following:

 

Capture.PNG

MynhardtBurger
New Contributor III

Re: Get a count from nested if statement

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