Complex Set Analysis for imbricated aggregation

[acaucheteux78]

Hello,

I manage several facts type in same table. Those facts share some properties in another table and one type of facts is linked to the other by a pivot table.

- A fact is linked to B fact

- A fact and B fact share some properties

I would like to create a KPI or representation of difference between all A and B linked.

Example :

A and B have common properties :

• name
• ref
• price

I would like to count the qty of A who has not the same value for name, ref and price than linked B...

My schema is :

[Fact] : contains all A and B facts

Fact_key

Fact_Type (A or B)

Fact_Counter

[Fact_properties] : contains all properties of facts

Fact_Key

Name

Ref

UnitPrice

[Pivot_AB] : create link between fact A and B

Fact_key

B_key

I succeed to create table representing each propertie for two types :

A Name	B Name	A Ref	B Ref	A Unit Price	B Unit Price
=IF([Fact_Type]='A',[Name])	=aggr(NODISTINCT only({1<[Fact_Type]={'B'}>} [Name]), B_Key)	etc...	etc...

But now, I want to create a KPI representing the consistency rate between A and B, so the number of facts A not consistent with linked B facts.

Impossible?

[JordyWegman]

Hi, 

Can you share a QVF? That would be a bit easier.

Jordy

Climber

[acaucheteux78]

The data load script is very complex (5 layers to load), so I created an simple example