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!

[vinieme12]

to count the deliveries that are not returned you should be using the flag that has been created

<<CORRECTION>>

Example

Deliveries that were not returned will be

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

Deliveries that were returned will be

Count({<Returned.ReturnedFlag={1}>}Distinct DeliveryID)     // equal to 1

[dafnis14]

Unfortunately , it doesn't work...

Count({<Returned.ReturnedFlag-={1}>}Distinct DeliveryID)

The returned quantity is also wrong...

[vinieme12]

just made a correction above

[dafnis14]

Not there yet... 😞

[vinieme12]

The returned quantity is also wrong...

How is it wrong when we are counting Distinct DeliveryID?

and you can see Customer 111 has returned only DeliveryID  =1 , so that is correct

if you want to see how many offers then change to Distinct  Returned.OfferID  or

distinct   Returned.ItemId to see how many Items were returned

[vinieme12]

Sorry; but you have to tell me clearly what you values you expect to see!

[vinieme12]

The returned quantity is also wrong...

How is it wrong when we are counting Distinct DeliveryID?

and you can see Customer 111 has returned only DeliveryID  =1 , so that is correct

if you want to see how many offers then change to Distinct  Returned.OfferID  or

distinct   Returned.ItemId to see how many Items were returned

[dafnis14]

for customer 111 it shows:

I attached the desired output in the post:

When a customer is selected, it should display the relevant quantities.

This is where I was stuck.. because I Had trouble to add a customer key..

Thanks!

[dafnis14]

Hi,

I think the issue is because there isn't a record for Delivery ID that has return items, like the ones created for 2 and 3.

That's why I can't count delivered items for delivery 1.

[vinieme12]

I don't know what you are trying to achieve now, can you tell me exact numbers that you expect to see!!

as i already gave you the expressions to use

to count the deliveries that are not returned you should be using the flag that has been created

<<CORRECTION>>

Example

Deliveries that were not returned will be

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

Deliveries that were returned will be

Count({<Returned.ReturnedFlag={1}>}Distinct DeliveryID)    // equal to 1

Can you post what expressions you are using?