Data missing on filter

[#1]

Sorry for the newbie question but I'm playing around with this.

See attached. 

What I have is 4 tables (Reseller, Orders, Forecast and Products)

As you'd imagine, the resellers will have orders (of products) and a forecast (of products).

If you open my test file, you'll see the forecast along side the number of orders submitted so far and a percentage of the forecast they've hit.

Looks great and is correct.

However, click on Canada to filter to only Canadians.  You'll see reseller #10 disappears.  My guess is because the forecast that reseller 10 is unique compared to the other Canadian.  It shows up when US and Canada are shown because another reseller has a forecast/order for the same product.

HELP!  I know I'm supposed to do some joining/concantenating here but I'm lost.  I've tried for many hours now but it never results in what I'm looking for.

[swuehl · Accepted solution]

Hi,

I think there are two problems:

a) a circular reference (please have a look at the table viewer CTRL-T) - I solved this by concatenating the order and forecast table

b) for orders, only product# are given, after concatenating, the link to product family is missing - I solved this by applying a mapping from product# to product family in product table also.

Please have a look at the attached.

Regards,

Stefan

[#3]

Thank you.  It makes a lot more sense now.