Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Set Analysis

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Set Analysis

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

2 Replies
MVP
MVP

Re: Set Analysis

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

Not applicable

Set Analysis

Thx Miguel - worked like a charm!

Community Browser