Set analysis using data from different filter panes

[Ethel]

Hi, could you please help me with this problem:

I have master dimensions(I created them) and I've created filters using master dimensions.

Filters are the following: Specialty, degree and rank.

I have master measures(I created them, but can I use my dimensions from table instead?)

I need to filter data in table with my master measures based on filters that end user choses(e.g. specialty1, degree1, rank1 )

For specialty only my formula looks like this:  Max({[specialty]}[measure]) It works.

I need to add degree and rank and ideally not use any aggregation. 

Thanks a lot in advance!!! 

[anthonyj]

Hi @Ethel ,

I'm not sure if I understand this one. Standard Qlik functionality means that any selection made in your filters will alter your master measures, so as you choose your Specialty, degree and rank this will cut down your measure.

Do you want to use the result of a measure as a lookup for a specific dimension value?

Max({<[specialty]>=}[measure]) 

This syntax would mean that any choice made in "specialty" is not going to affect "Measure". If you want to add degree and rank it would look like this.

Max({<[specialty]>=, degree=, rank=}[measure])

I'm sorry but I don't quite understand your requirements.

Thanks

Anthony 

[Ethel]

My report looks like this. End users choose filters from filter pane (Specilaty, Rank and Degree). As a result salary is calculated in the final table. Right now My measure salary is calculated like this: Max({[specialty]}[measure]) . Qlik see which specialty is chosen and shows number for specialty. But I don't know how to add Rank and Degree in this formula so it will see them also. 

Thanks a lot in advance!!!!

[anthonyj]

Hi @Ethel ,

This might affect your whole solution but now I've got some context behind what you're doing I can see what you're after.

Once a combination of Specialty, Rank and Degree has been selected you want to show the relevant salary. (Correct me if I'm wrong here) And you don't want to show the salary until all 3 have been chosen.

My assumption here is that there is one salary for every combination of the 3 columns. I've added conditions that require all 3 to be chosen before a salary will appear. Until then the message prompts to choose all 3.

Until all 3 are chosen the message remains.

Once all 3 are chosen a salary appears and the title is updated with the selections.

Measure is:

=if(GetSelectedCount(Specialty) = 1 and GetSelectedCount(Rank) = 1 and GetSelectedCount(Degree) = 1,
'Salary for ' & GetCurrentSelections() , 'Make Selection')

Measure title:

Measure is:

=if(GetSelectedCount(Specialty) = 1 and GetSelectedCount(Rank) = 1 and GetSelectedCount(Degree) = 1,
'Salary for ' & GetCurrentSelections() , 'Make Selection')

The data model I used is a straight table.

data:
load * Inline [
Specialty, Rank, Degree, Salary
Biostatistics, Chair, M.D., 10000
Chairman, Instructor, PHD, 25000
Dermatology, Chair, M.D., 30000
Biostatistics, Instructor, M.D., 13000
];

Sorry for any incorrect assumptions, but I hope it helps.

Thanks

Anthony

[Ethel]

Hi Anthonyj,

thank you very much!

But I have alternative states for each filter . Specialty, degree and rank are alternative states and formula is not working with them.

[anthonyj · Accepted solution]

Hi Ethel,

If you're using the above logic then alternate states wouldn't be required. However, if you still require alternate states in your visualisations and you want to pass the value selected into your original state there is a specific syntax required in your set analysis.

Max({<Specialty = [State 1]::Specialty >}[measure])

With this syntax whatever you choose from Specialty in your alternate state will be passed through to your default state.

There's a number of articles that I go back to for in depth information on advanced set analysis. One of them is this from living Qlik. It covers the syntax required to pass selected values between states.

http://livingqlikview.com/livingqlik-roots-the-ultimate-qlikview-set-analysis-reference/

I hope this helps.

Thanks

Anthony

[Ethel]

This article is amazing! Thank you!