Conditional Expression: Count If

[jacqueshol]

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

[MK_QSL · Accepted solution]

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

[jacqueshol]

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

[jacqueshol]

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