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

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

    Chrsitine Hill

      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?