1 Reply Latest reply: Mar 23, 2011 7:18 PM by Chrsitine Hill

# Stop from limiting results based on fields that are not joined?

This is a very confusing problem, so hopefully I can explain it so someone else is able to understand...

Let's say I have table REGION and table STORE. On each table I have Date, StoreID, City, and Brand - these are all joined. In addition, on the STORE table I have Supplier, DiscountAmt, etc... Here is my fake example:

I created a summary table that shows the total # of sales for STORE (50) and total # of sales for REGION (100).

I select the following criteria in QV:

1) Brand 'ABCD' --> the new totals are STORE=25 and REGION=75

2) City 'Chicago' --> The new totals are STORE=10 and REGION=60

3) Supplier 'DCBA' --> The new totals are STORE=5 and REGION=30 !!!!! This is incorrect, since the REGION does not have the field Supplier. So selecting this field should have no impact on the REGION totals (ie - it should stay 60).

I think I figured out why it's doing this, but I can't figure out how to stop it. Since only Store 'BACD' uses Supplier 'DCBA', it automatically limits the Store field to 'BACD' even though I didn't select anything. This unintentionally reduces the REGION total to only include that store.

I tried using set analysis with {1}, but that keeps the original total of 100, which I don't want. Then I tried limiting by adding <Field=> to the set analysis, but that didn't do anything. It still keep the original total of 100. My formula without the set analysis is just Sum(REGION.Sales)

Any ideas?

• ###### Stop from limiting results based on fields that are not joined?

OK, I just figured it out... I still had a 1 in there instead of \$. This formula is working:

Sum({\$<[STORE.Supplier]= >} [REGION.Sales])

Then my next question: I have about 20 other fields I'm pulling in that I would want it to ignore. Is there a way to write the opposite of this & including only my joined fields instead of adding all those fields to the formula? (other than entering all the values I want?)