I'm new to QV and I'm battling with the set analysis function. My QV application is for a drilling company (4 different companies) and I have joined the Journals and Documents table from SQL. My problem is that a project only appear in the journal fact table once an invoice or credit memo are posted but appear in the documents fact table for all documents. To distinguish (create a link) for the different projects and different companies I have added a prefix to each project (eg 'SA' for the South Africa and 'GH' for Ghana. So in my QV script I have - Left(Project,2) as JCompany on the Journals Load and Left(Project,2) as TCompany on the Documents load.
When I sum for 'MetersDrilled' I want the set analysis to disregard the fact that the current selection for company = JCompany but instead to calculate 'MetersDrilled' based on the selection Company = TCompany.
The part in italics is where I'm struggling. So in other words I want the application to disregard the current selections except for ItmsGrpCod = 137
and TCompany = 'SA, GH , WA or DR' and ObjType = 13 or 14. If I use 1 instead of $ I get the right result but then the the total remains the same when I select a different value from the Project choose list or when I select a different month etc.
So I guess my question is - I want the sum(Qty) to change when I select other dimensions but it must always be based on the Company selected in TCompany and disregarding JCompany.