9 Replies Latest reply on Feb 1, 2017 6:29 AM by Faris Ahamad M

# Counting the no.of Accounts whose sales is greater than zero

Hi,

Please find the snapshot below. I am trying to count the no.of Accounts whose sales is greater than zero but i am getting the count including zero sales also. Please help me to find the  count of accounts whose sales is greater than zero for each city.

Ex: For City A, the count of Accounts whose sales is more than \$0 should be 2 but i am getting 3. It is including accounts having zero sales as well.

 City Account Sales Date A Account 1 \$100 16-Jan B Account 2 \$200 17-Jan C Account 3 \$50 18-Jan A Account 1 \$0 19-Jan A Account 5 \$0 20-Jan B Account 6 \$500 21-Jan C Account 7 \$0 22-Jan A Account 8 \$400 23-Jan

• ###### 1. Re: Counting the no.of Accounts whose sales is greater than zero

I am using the below formula to calculate the count:

IIF(Sales>0,countd(Account,0),0)

Output: 3

Correct output should be : 2

• ###### 2. Re: Counting the no.of Accounts whose sales is greater than zero

Another solution is: IF { FIXED [City],[Account]:SUM([Sales])} > 0 THEN 1 END. You would sum that calculated field to get the result.

• ###### 3. Re: Counting the no.of Accounts whose sales is greater than zero

Hi Ayesha,

Can you attach the package so that the forum participants get exact data type while accessing the data.

thanks,

Dhruv

• ###### 4. Re: Counting the no.of Accounts whose sales is greater than zero

Hi Dhruv,

@Andrew,

Thanks for your reply. With the help of your reply i could understood the concept of fixed but it is still not giving me the desired result.

• ###### 5. Re: Counting the no.of Accounts whose sales is greater than zero

Hi Ayeesha,

try:

COUNTD(

if [Sales]>0

then([Account])

END

)

• ###### 6. Re: Counting the no.of Accounts whose sales is greater than zero

Please provide a screenshot so we can see how you're using it.

• ###### 7. Re: Counting the no.of Accounts whose sales is greater than zero

COUNTD(

if [Sales]>0

then([Account])

END

)

it's working!!

• ###### 8. Re: Counting the no.of Accounts whose sales is greater than zero

Hi All,

Thanks for all your reply. I got the solution. Problem was at the back end data. It had few nulls because of which i was getting wrong result.

Appreciate all of you for sharing your time and thoughts.

Thanks,

Ayeesha

• ###### 9. Re: Counting the no.of Accounts whose sales is greater than zero

Hi Ayeesha,

Can you please explain what was the exact problem with the back end data.?

Because I tried using your calculation :

existing solution:

IIF(SUM([Sales])>0,countd([Account]),0)

And got the same error output as yours.

Then i used my calculation         :

No of accounts :

COUNTD(

if [Sales]>0

then([Account])

END

)

and it worked as per your requirement.