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)
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?)
