Conditional Expression: Count If

jacqueshol — Wed, 25 Nov 2020 16:16:04 GMT

Hi there,

I have Accpac data in which the Fuel Price is captured as a GroupCode with value 9908. Other GroupCode values are 9901 (Trading Days), 9902 (Wage Weeks), 9906 (Kilometres), etc.

I want to count the distinct number of cost centres for which the Fuel Price is larger than 0. I have used the following expression, but get an error message: "Nested aggregation not allowed":

=count(distinct if(sum({<[GroupCode]={'9908'}>} TRANSAMT)> 0, [Cost Centre]))

I've always imported the Fuel Price through an Excel sheet and used the following expression which worked well:

=count(distinct if(FuelPrice>0,[Cost Centre]))

The Fuel Price is now captured directly into Accpac under the GroupCode of 9908 and not available in Excel any longer. In other words, what used to be FuelPrice is now [GroupCode]={'9908'}. I have therefore also tried the following to avoid the nested problem:

=count(distinct if([GroupCode]={'9908'}> 0, [Cost Centre]) )

but this is erroneous as well. Error message: "Error in expression: ')' expected.

Can anyone please assist me? I really need to count (distinct) the number of cost centres for which the fuel price is larger than zero. It would be greatly appreciated.

Take care,

Jacques

Re: Conditional Expression: Count If

MK_QSL — Mon, 03 Jul 2017 14:58:31 GMT

Count({<[Cost Centre] = {"=SUM({<GroupCode = {'9908'}>}TRANSAMT)>0"}>}Distinct [Cost Centre])

Re: Conditional Expression: Count If

jacqueshol — Mon, 03 Jul 2017 15:05:40 GMT

Oh my goodness, I wasn't expecting an answer so quickly. Let me try it and see if i can get it to work.

Re: Conditional Expression: Count If

jacqueshol — Mon, 03 Jul 2017 15:07:49 GMT

No way! It works perfectly. Thank you kindly Manish! You have really helped me out. I sincerely appreciate your assistance.

topic Re: Conditional Expression: Count If in QlikView

Conditional Expression: Count If

Re: Conditional Expression: Count If

Re: Conditional Expression: Count If

Re: Conditional Expression: Count If