2 Replies Latest reply: Jun 1, 2011 10:43 AM by DaniePosthumus RSS

    Set Analysis



      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.


      =Sum({$<ItmsGrpCod={'137'},TCompany = {'SA','GH','WA','GR'},ObjType={'13',14'}>}Qty)


      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.