Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
acaucheteux78
Contributor
Contributor

Complex Set Analysis for imbricated aggregation

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 NameB NameA RefB RefA Unit PriceB 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?

2 Replies
JordyWegman
Partner
Partner

Hi, 

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

Jordy

Climber

Work smarter, not harder
acaucheteux78
Contributor
Contributor
Author

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