Count based on another Field

[mskusace]

I would like to count a column called "ID" based on the criteria of another column "Color". The problem is, I have another table that is associated and has the same "ID", but multiple instances because it has another field called "ROLE".

I would like to count the UNIQUE "ID"s based for "Color" =  "RED". It is actually the word "RED", so text.

The table below would yield 3 and 50%. How can I do this? I am trying to use the KPI chart.

ID	Color
WA13	GREEN
WB14	RED
WC15	ORANGE
ZS15	GREEN
DB09	RED

The other associated table

ID	ROLE	USER
WA13	MANAGER	USER1
WA13	SUPERVISOR	USER2
WA13	ANALYST	USER3
WB14	ANALYST	USER3
WB14	MANAGER	USER4
WC15	SUPERVISOR	USER5
WC15	MANAGER	USER6
WC15	SECRETARY	USER7
ZS15	MANAGER	USER4
ZS15	ANALYST	USER8
ZS15	SUPERVISOR	USER2
ZS15	SECRETARY	USER9
DB09	SECRETARY	USER10
DB09	SECRETARY	USER11
DB09	ANALYST	USER12

[rogerpegler · Accepted solution]

The count of unique ID's where Color is RED is 2, being WD14 and DB09. To get this use:

count({<Color={'RED'}>}DISTINCT ID)

The total count of all ID records where Color is RED would be 5 based on the instances of the same two IDs in the role table. To get this use:

Count({<Color={'RED'}>}ID)

I'm not sure how your desired results of 3 and 50% relate to the sample data.

In general though to avoid possible unexpected results (eg another unexpected linked table), I would use the load script to duplicate ID into another field in the Color table and count that field.

[ToniKautto]

Not sure what 3 and 50% represents in your data.

As best practice I would suggest that you never use key fields in aggregations. This will make it easier for you to anticipate the aggregation results. For example prefix your keyfields with % to make them easy to avoid and hide. 

%ID	ID	Color
WA13	WA13	GREEN
WB14	WB14	RED
WC15	WC15	ORANGE
ZS15	ZS15	GREEN
DB09	DB09	RED

%ID	ROLE	USER
WA13	MANAGER	USER1
WA13	SUPERVISOR	USER2
WA13	ANALYST	USER3
WB14	ANALYST	USER3
WB14	MANAGER	USER4
WC15	SUPERVISOR	USER5
WC15	MANAGER	USER6
WC15	SECRETARY	USER7
ZS15	MANAGER	USER4
ZS15	ANALYST	USER8
ZS15	SUPERVISOR	USER2
ZS15	SECRETARY	USER9
DB09	SECRETARY	USER10
DB09	SECRETARY	USER11
DB09	ANALYST	USER12

count({<Color={"RED"}>} ID)  will return 2

count({<Color={"RED"}>} USER) returns 5

count(DISTINCT {<Color={"RED"}>} USER) returns 4

[mskusace]

count({<Color={'RED'}>}DISTINCT ID) worked perfectly. 

I'm not quite understanding why to duplicate ID into another field as opposed to using that field directly. So you are saying that to remove any association with any other tables and use the duplicated field since it has no association with other tables?

[mskusace]

So now I would like to add more complexity to it. I want to count distinct ID for all COLOR = 'RED' for all STATUS <>'FINAL'. I realized I was doing some set formulas which did not display my data on the table, but I believe it is still being used in the calculation.

[abhi1693r]

Hi Stephan,
I had a similar issue. So I have two columns, PART AND CUBES, with one to many relationship.(one PART can have many CUBES or a PART can have no CUBE).
Scenario: If I select 3 PARTS which have 2 CUBES, wherein 2 PARTS have 2 CUBES and 1 PART has no CUBE. I want a count of all the CUBES plus any PART which has no CUBES.

PART	CUBES
SW1001	1415TV
DC1003	1516TD
BW1002

So the count should be 2+1=3.
How can I implement this?

Regards,
Abhishek

[mskusace]

So if this was your sample data set was below, what would you expect as the count? 4?

PART	CUBES
SW1001	1415TV
SW1001	1714DC
DC1003	1516TD
BW1002

[abhi1693r]

Yes, I would expect the count to come out as 4.

[mskusace]

Why not just do a count of "PARTS" and do not use distinct so it counts all the duplicates? That may get you what you want. If I misinterpreted, please let me know.

Count(PARTS)

[abhi1693r]

 So, this is just one example scenario. I am actually supposed to count the CUBES and any PART which does not have a CUBE. Hence, 3+1.

Tried a couple of countif statements, but no luck.