Complex Set Analysis for imbricated aggregation

acaucheteux78 — Sat, 16 Nov 2024 03:52:57 GMT

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?

Re: Complex Set Analysis for imbricated aggregation

JordyWegman — Tue, 10 Dec 2019 09:24:06 GMT

Hi,

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

Jordy

Climber

Re: Complex Set Analysis for imbricated aggregation

acaucheteux78 — Wed, 11 Dec 2019 08:41:32 GMT

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

topic Re: Complex Set Analysis for imbricated aggregation in App Development

Complex Set Analysis for imbricated aggregation

Re: Complex Set Analysis for imbricated aggregation

Re: Complex Set Analysis for imbricated aggregation