AND-mode in Set Analysis / Multiple criteria must be fulfilled

[adlim91]

Hi all, 

I have this the following sample dataset. My design is to have several filter panes, with them I can get Sales per Industry. 

A brief explanation on the data: each industry has four scenarios, and each scenario has a matrix score. Each industry has a sales value. 

Scenario	Industry	Matrix	Total Sales per Industry
1	Banks	1	50
2	Banks	1	50
3	Banks	1	50
4	Banks	1	50
1	Industrials	1	130
2	Industrials	1	130
3	Industrials	0	130
4	Industrials	0	130
1	Services	1	200
2	Services	1	200
3	Services	0	200
4	Services	0	200
1	Engineering	1	200
2	Engineering	1	200
3	Engineering	1	200
4	Engineering	1	200

In my assignment, I will create filter panes, one for Scenario, one for Matrix.  I am supposed to play around the filters to do some data recovery. In the ideal case, I can filter however I want, say to get the industry's sales with Scenario 1 and 2 having a Matrix of 1, or Scenario 1, 2 and 3 with a Matrix of 0.

As an example, when I select all Scenario 1, 2, 3 and 4 and Matrix of 1, only Banks and Engineering should be filtered in.

For starting, I would have this in a table. How can I achieve this?

Thanks lots!

[JordyWegman]

Hi Adlim,

Do you want the result in a table with only the industries with all matrix's that are 1? Or do you want a count of the industries?

The best thing would be to create this in the script with an indicator.

mapIndustryMatrix2Industry:
Mapping Load
  Industry,
  IF(_cntIndustry = _cntMatrix,1,0) as _indIndustryMatrix
;
Load
  Industry,
  Count(Industry) as _cntIndustry,
  Count(Matrix) as _cntMatrix
From [YourSource](qvd)
Group by Industry
;

Table:
Load
  Scenario,
  Industry,
  Matrix,
  Total Sales per Industry,
  ApplyMap('mapIndustryMatrix2Industry'),Industry,0) as _indIndustryMatrix
From [YourSource](qvd);

Now you can use the _indIndustryMatrix as a filter in your tables to only show the industries you want or use this in a formula to count the amount of industries:

Count(Distinct {$< _indIndustryMatrix = {1}>} Industry)

Jordy

Climber

[tresesco]

You can try like:

Sum({<Industry={"=Count({<Matrix={1}>}Industry)=4"}>} [Total Sales per Industry])

 

[lironbaram]

hi 

this expression will do the trick 

sum({<Industry={"=only(Matrix)=1"}>}[Total Sales per Industry])

it will only use industries where the only value in the field Matrix is 1 

the advantage of using this method is if you add or remove  scenarios it will still work 

unlike counting 4 scenarios 

[adlim91]

In my assignment, I will create filter panes, one for Scenario, one for Matrix.  I am suppose to play around the filters to do some data recovery.

In the ideal case, I can filter however I want, say to get the industry's sales with Scenario 1 and 2 having a Matrix of 1, or Scenario 1, 2 and 3 with a Matrix of 0.

[lironbaram]

you describe one thing at first 

now you describe something else 

so it's not really clear what you are after

[adlim91]

My apologies for not being clear enough at the beginning. I have a more complicated dataset and I was trying to simplify it in my question, somehow I lost sight of clarity.

But essentially the goal is the same - to be able to filter for results based on Scenario and Matrix, but only for industries fulfilling both criteria on the selected Scenario and Matrix. I see some very much appreciated guidance provided, but in my case it needs to be a little more dynamic - that I can make selections in filter panes and get the results.

Thanks again!

[adlim91]

I still have trouble figuring this out, anyone more experienced can help?

Or is it not possible to achieve in QlikSense?

Thanks!

[tresesco]

Could you explain the expected output against your few possible selections?

[adlim91]

Sorry that I had not been very clear, below are a few examples of my desired outcome:

When Scenario = 1,2,3,4 ; Matrix = 1:

Scenario	Industry	Matrix	Total Sales per Industry
1	Banks	1	50
2	Banks	1	50
3	Banks	1	50
4	Banks	1	50
1	Engineering	1	200
2	Engineering	1	200
3	Engineering	1	200
4	Engineering	1	200

When Scenario = 1,2 ; Matrix = 1:

Scenario	Industry	Matrix	Total Sales per Industry
1	Banks	1	50
2	Banks	1	50
1	Industrials	1	130
2	Industrials	1	130
1	Services	1	200
2	Services	1	200
1	Engineering	1	200
2	Engineering	1	200

When Scenario = 2,3 ; Matrix = 1:

Scenario	Industry	Matrix	Total Sales per Industry

Essentially, all the Scenario selections of the Industry would have to fulfil the Matrix selection, then only the particular specific data would show up. My problem now is, it is only working the results in OR logic, which I don't want to see e.g. 

When Scenario = 2,3 ; Matrix = 1:

Scenario	Industry	Matrix	Total Sales per Industry
2	Banks	1	50
3	Banks	1	50
2	Industrials	1	130
2	Services	1	200
2	Engineering	1	200
3	Engineering	1	200

Anyone has any idea how I can formulate my expression? Thanks!