Set Analysis

Wed, 01 Jun 2011 09:28:28 GMT

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

Re: Set Analysis

Miguel_Angel_Baeyens — Wed, 01 Jun 2011 09:48:15 GMT

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.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Set Analysis

Wed, 01 Jun 2011 14:43:37 GMT

Thx Miguel - worked like a charm!

topic Set Analysis in QlikView

Set Analysis

Re: Set Analysis

Set Analysis