Inconsistent count in universe & webi

[former_member672670]

Hie Guys,

I have a object named "Manager ID" in my Universe. It is defined as a Dimension (Char). Now, I have created a Measure (Num) object in the Universe to count the Manager ID. The formula for the Measure Object is given below.

No. of Managers = Count(Select Manager ID) from Class A.

Now, in my Webi Report, I have 3 columns - Department Name, No. of Managers & Count(Manager ID).

The problem is - The column values for No. of Managers is not the same as Count(Manager ID) in the report.

Could anyone please help me understand the issue and suggest how to resolve it? Thanks.

[Former Member]

Hi Jeewan,

As you are using count(manager_id) in the SQL, it is grouping the count as per the columns used in group by clause

and giving you the grouped count in report level.

At report level, you are simply counting distinct manager_ids; however, there is no grouping taking place as far as i understand.

Try this:

1. Create two seperate queries.

2. In one query, choose objects:  Department Name, No. of Managers

3. Check the count.

4. Create another query:  Department Name, Manager ID

5. Try counting (manager_id) at report level and check.
6. Compare the counts in both scenarios and check.

Hope it will help.

Regards,

Yuvraj

[former_member672670]

"Count Distinct Manager ID" for the measure object  is counting duplicate values in the Universe. Projection for the measure is Sum.

In Webi, Count(Manager ID) is counting unique values.

[Former Member]

If you are using count(distinct table.column) in your meaure at the universe, then your projection should be set to none.

[former_member672670]

I tried your solution by setting the projection to none, but now I am getting the following output in Webi.

Dept Name	No. of Managers
A	1
A	1
A	1
B	1
B	0
C	1
D	1

I did a break on Dept Name and then Sum the total. But, it is still not matching Count(Manager ID) in Webi.

For some reason, Count(Distinct Table Name Manager ID) in the Universe is still counting duplicate IDs. The table is a derived table from MySQL database.

[Former Member]

OK, so how many managers should there be in Dept A?

[former_member4998]

For some reason, Count(Distinct Table Name Manager ID) in the Universe is still counting duplicate IDs.

@ Universe if you use Count (DISTINCT Table. Field)

Data Type: Numeric

Projection Function: SUM

It's always gives correct results. maybe your something missing.

Can you  provide the

SQL Code and screen-shot @ universe level

SQL Code and screen-shot @ SQL Developer

[Former Member]

Count(Distinct table.field) should not use projection of sum

Consider the following:

I want to analyse customer purchases of products

January:

Customer A buys a shirt, a bag and a coat

Customer B buys a shirt and a coat

February:

Customer A buys two bags and a hat

Customer B buys a bag and two coats

I only have two customers in total but the projection of sum will take me well beyond this.

[former_member672670]

Column values for Dept A when using "Manager Count" object from Universe (Select Count Distinct & Projection as None)

Dept Name	Manager ID	Manager Count
A	100	1
	101	1
	102	1
	102	1
		0
		0
		0
Sum:		4

Column values for Dept A when using Count(Manager ID) in Webi:

Dept Name	Manager ID	Count(Manager ID)
A	100	1
	101	1
	102	1
		0
Sum:		3

As you can see in the 1st table, Manager Count -"Count Distinct Manager ID" object from the Universe is counting duplicate values for Manager ID = 102. Thus, the sum is not correct in table 1. Even when I set the Projection of Manager Count as Sum, it counts duplicate Manager IDs.

[Former Member]

Why have you got blank rows for Dept Name and Manager ID - do you have managerless employees (apart from the two Managers)?

=Count([Manager ID]) in ([Dept Name]) should work as a total.

[former_member672670]

Some projects do not have managers assigned to them which is why they are blank.

I am able to get the correct results by counting manager ID in Webi, but the Count(Distinct Manager ID) measure object from the Universe is not giving unique count in Webi.

[Former Member]

It won't pull through correctly. Count distinct is a very different proposition to Count. Count is a very basic function.

[Former Member]

What projection are you using for your count object?

If you are using count, use sum

If you are using count distinct, use none.

Counting is not as straightforward as summing when it comes to balancing back to the universe.

[former_member4998]

Hi

Change the SQL code to below

@ Universe

Count (DISTINCT Table. Field)

Data Type: Numeric

Projection Function: SUM

@ WbI

Count(Distinct[Manager ID])

Create a new report and test showing both universe level measure and webI level measure getting same results or not

Still if you get difference, need to check Manager ID is compatibility with all the other object’s using in the webi results objects

[former_member672670]

Count (Distinct) did not resolve the issue for me. It produces the same result.

Also, I am not getting any incompatibility error in my Webi query panel. Any other reason why this could be happening?

[Former Member]

Hi Jeewan,

at report level  use below function

=Count([Manager ID];All)

at universe level use distinct count

You will get DISTINCT count in universe as well webi.

Regards,

Samatha B

[former_member4998]

Normally it should you give same results. If manger id is compatibility with Department name..

First you need to find out which results are correct(universe level/webi level) with checking database.?

1. then create report Department Name, No. of Managers & Count(Manager ID). And drill down and find out the difference manger ID b/w universe level object and webi level object.

1. Create a report using Department Name, No. of Managers and check the generated SQL, using the generated SQL check DB SQL editor. Getting No Of Mangers sum is 2

Dep Name	No . of Managers
abc	1
xyz
Jeewan Pandey	1
	Sum 2

1. Create a report using Department Name, Count(Manager ID) and check the generated SQL, using the generated SQL check DB SQL editor. Getting No Of Mangers sum is 3

Dep Name	Count(Manager ID)
abc	1
xyz	1
Jeewan Pandey	1
	Sum 3

1. Create a report using Department Name, No. of Managers & Count(Manager ID) and check the results. And find out where discrepancy

Dep Name	No . of Managers	Count(Manager ID)
abc	1	1
xyz		1
Jeewan Pandey	1	1
	Sum 2	Sum 3

[Former Member]

Hi Jeewan,

try distinct count at universe level,

count(distinct Manager ID)

Regards,

Samatha B