# Business Objects: Count v CountAll

I’m going to quickly run through the difference between the Count and CountAll function. Note that the CountAll function is only available in Full Client. Web Intelligence provides the Count function with a set of parameters, which I will discuss later.

Let’s take a simple example data set:

Two columns, one called Name and the other called Function.

Lets create eight rows of tab delimited data in a text file as follows:

Note that the seventh row does not have a persons name. Let us assume this is a position that needs to be filled. The eighth row has a name and no function.

If we load this data into BusinessObjects, we get the following result:

What result do you expect if you perform a count on the name column? Remember, you have loaded 8 rows.

If you guessed 6, you are correct.

To prove this, select the n icon from the toolbar or right click on the column and select Calculations/Count, an additional row is added to the table with the value 6. If you take a look at the formula behind the value 6, you will see it is * =Count(<Name>)*.

Now if you perform a CountAll on the name column, what value do you expect to see?

If you guessed 7, you are wrong. Why? Because CountAll counts all rows, including duplicate values and empty rows.

To prove this, right click again on the column and select Calculations/CountAll. An additional row is added to the table with the value 8.

We can repeat this exercise on the Function column. Remember, we loaded 8 rows. What will be returned if you run a Count on the Function column?

If you guessed 7, you are wrong. Count will return a count of DISTINCT rows. So while you have loaded 8 rows, two of those contain Sales. Hence the Count will return one count of Sales. Also note it will not count empty rows.

To prove this, select the n icon from the toolbar or right click on the column and select Calculations/Count, an additional row is added to the table with the value 6. If you take a look at the formula behind the value 6, you will see it is * =Count(<Function>)*.

So what value will be returned if we perform a CountAll?

If you guessed 8, you are correct. CountAll will return a sum of all rows, including duplciates an empty rows.

# Web Intelligence: Count

If you are using Web Intelligence, you will not have a CountAll function. Instead you have a Count function two parameters:

**Count([object]; [INCLUDEMEPTY]; [DISTINCT|ALL])**

Using our example data set, what will * Count([Name])* return?

The answer is 6. * Count([Name])* will return a distinct list of Names.

What do you think will be returned by* Count([Name];[INCLUDEEMPTY])*?

If you guessed 8, you are wrong. If you guess none, well done.

Why? Okay, I slightly misled you. The syntax of the formula is incorrect. If you are including a parameter, do not use the square brackets for the parameter. The correct formula is: * Count([Name];INCLUDEEMPTY)*.

This will bring back 7 rows. Six distinct rows plus an empty row.

So how many rows are counted using * Count([Name];INCLUDEEMPTY;DISTINCT)*?

The answer is 7. You bring back all the distinct rows (there are six – John is duplicated), and one empty row.

So what will * Count([Name];INCLUDEEMPTY;ALL)* bring back?

The answer is 8. All rows, including duplicates and empty rows.

There are valid scenarios where you may want to use the * CountAll* or

*function. It is important to be clear on where these functions should be used, as used incorrectly, they could bring back incorrect results.*

**Count([Name];ALL)**Did you find this type of article useful? Are there any other functions that you would like me to walk through? If so, please let me know via a comment. Thanks for reading!

August 5, 2012 at 3:59 pm

GOOD

September 20, 2012 at 11:46 am

Good Explanation and Thanks

January 28, 2013 at 6:32 am

Very good stuff

September 17, 2013 at 1:46 pm

Thanks,

Can you advice on a similar problem.

The below formulas contains client Id

[ct-CashNonInc]= 67544

[ct-CashInc]= 67544,12345,23145

I would like to create a formula to show a count of 3

The formula below shows 4

=Count([ct-CashNonInc];Distinct) +Count([ct-CashInc];Distinct)

October 1, 2013 at 11:56 am

What you need to do here is somehow create a master list of all values in both variables and then do a distinct count. I’m not quite sure how you would do this in the Business Objects/Web Intelligence front end.

Unfortunately I don’t have an answer, but some here are some thoughts that might help you discover a solution:

Create a union query with one measure in each query. You’d then have a distinct list of values over which you can generate a distinct count.

Create a universe object that pulls together the values held in these two fields using some SQL code. Use that object in your distinct count.

November 13, 2013 at 4:24 pm

I have just used the Count and CountAll. Thanks for the insight it helped a lot.

November 14, 2013 at 12:56 pm

I’m glad it helped you.

July 11, 2014 at 1:02 pm

Great stuff, exactly what I was looking for. Thank you

April 21, 2015 at 8:56 am

Greatly appreciated. Simple and brief example yet it covers a lot.

Rod

June 12, 2015 at 8:49 pm

Thanks, good explanation as it was driving me mad!