Need help to calculate a KPI based in counting distinct company codes

[Anonymous]

Hello everyone,

  We are facing a big issue in a QV project, mainly with one KPI, named “Number of Distinct Companies” – Ideally COUNT(DISTINCT CMT_NNUMSCH)

An example of a data table is the following (group by activity):

ACTIVIDAD	Total
Total	3669
0940 VEHICULOS REPUESTOS Y ACCESORIOS FAB MAY MIN OFI	3669
0941 VEHICULOS REPUESTOS Y ACCESORIOS MAY	245

But this is not what we want, as one company has to be counted just once in the whole table. It should be this way (I will explain why later):

ACTIVIDAD	Total
Total	3669
0940 VEHICULOS REPUESTOS Y ACCESORIOS FAB MAY MIN OFI	3424
0941 VEHICULOS REPUESTOS Y ACCESORIOS MAY	245

The activities are organized in a tree mode, here you are an example of the hierarchy that depends from 0940 and 0941.

ACT_0	ACT_1	ACT_2	ACT_3	ACT_4	ACT_5	ACT_6
940	941	887
940	941	946	181
940	941	946	4057	933
940	941	946	4057	3696
940	941	946	4057
940	941	946
940	941	950	190
940	941	950	191
940	941	950	3730
940	941	950
940	941	2879
940	941

  This means that if a company is associated to 0941 (of any of its sons) it is associated also to 0940. The KPI logic defines that the company has to be considered in the deepest level of the hierarchy (ACT_6 is the deepest). So, for example, all the companies that are associated to level 1 (245 different companies), need to be counted in row 0941, not in row 0940.

  We have made many tests and we are not finding the solution. Note: There is a field named NIVEL_HIJO (from 0 to 6) that informs the level of each hierarchy.

  I am sending an example of the QV app.

  Can anybody help me? It is becoming a nightmare.

Thanks in advance,

Javier