How to link 2 Fact tables that share Hierarchy and a key

[dafnis14]

Hi,

After exploring numerous blogs and discussions (like the one below)

https://community.qlik.com/message/547634#547634

I still couldn't figure out a solution for my issue..

I have a balanced  Hierarchy:

Delivery > Offer >Item

One fact table (Deliveries) is link to the highest level (DeliveryID) and one (Customer Returns) to the lowest (Key_Delivery_Offer_Item).

For both I need to sum by the item level which is working fine:

But when I add the customer key, I get a circular reference:

I think Generic Key or concatenation is not a solution because I need all the hiearchy levels for both fact tables.

Any idea how to handle this?

Thanks!

[dafnis14]

count({<Returned.ReturnedFlag={1}>}DISTINCT DeliveryID)

Please note that here I need summary, so customerID is not a dimension.

I need to know how many items were returned

Thanks!

[vinieme12]

try

count({<%KEY_ReturnedDelivery={"=(Returned.ReturnedFlag)=1"}>}DISTINCT DeliveryID)

[dafnis14]

I did...

But it shows items that were not returned as well:

Thanks!

[vinieme12]

As i said earlier, you cannot have ItemName as dimension in the Delivered table

[vinieme12]

if you actually had Item details in the Delivered table then we could have created a key that had CustomerID+DeliveryID+ItemID

but your data sample simply doesn't has that info, so you can only use ItemName in the returned info table

[dafnis14]

The reason I don't create the details for deliveries is because it'll be a huge file.

But the problem now is with the returned items, and they have itemID.

Sorry, I don't understand...

[vinieme12]

let me check

[vinieme12 · Accepted solution]

You are using the wrong ItemName field, you should be using Returned.ItemID

I've added ItemName to the returned table, you can choose to join or Keep them in a separate table.

I've just commented the Join statement in the script

[dafnis14]

Thanks again for your huge efforts!

So, basically, I cannot show returned & delivered metrics in the same table, only separate?

[vinieme12]

Nope You can't