Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
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.
Regards
Danie
Hello Danie,
If I understood right, you only want to ignore selections done in field JCompany, then the following expression should work:
Sum({$< JCompany =, ItmsGrpCod = {137}, TCompany = {'SA', 'GH', 'WA', 'GR'}, ObjType = {13, 14} >} Qty)
Hope that helps.
BI Consultant
Hello Danie,
If I understood right, you only want to ignore selections done in field JCompany, then the following expression should work:
Sum({$< JCompany =, ItmsGrpCod = {137}, TCompany = {'SA', 'GH', 'WA', 'GR'}, ObjType = {13, 14} >} Qty)
Hope that helps.
BI Consultant
Thx Miguel - worked like a charm!