Exclude dimension value but don't respect selections in that dimension

dselgo_eidex — Sat, 16 Nov 2024 03:14:03 GMT

I'll be honest, I don't really know how to title this one properly. I am working with a set of student test data with the following properties:

Every student has a unique ID
Every student may or may not have an Ethnicity assigned to them
Every student has a Gender
Every student takes either the Math test or the Reading test

My goal is to create a pivot table that does a side-by-side comparison of the count of students in an Ethnicity that took the test vs the count of students NOT in that Ethnicity that took the test. The table has 1 row dimension, 1 column dimension, and 2 measures (one for the count of students in the ethnicity and one for the count of students not in the ethnicity). About 2 months ago I asked a question on how to calculate a measure over the complement of the dimension value (https://community.qlik.com/t5/Qlik-Sense-App-Development/Calculate-measure-over-complement-of-dimension-value/m-p/1661589#M49680). This helped me to get the following expression for calculating the complement count of students for an ethnicity

pick( match(Ethnicity, $(='''' & concat(DISTINCT Ethnicity,''',''', Ethnicity) & '''')), $(=concat({$<Ethnicity -= {''}>} DISTINCT 'count({$<Student = E({$<Ethnicity = {''' & Ethnicity & '''}>} Student)>} TOTAL<Subject> DISTINCT Student)',',',Ethnicity)) )

After the dollar-sign expansion, it results in the following:

=pick(match(Ethnicity, 'African American','Asian','Hispanic','White'), count({$<Student = E({$<Ethnicity = {'African American'}>} Student)>} TOTAL<Subject> DISTINCT Student), count({$<Student = E({$<Ethnicity = {'Asian'}>} Student)>} TOTAL<Subject> DISTINCT Student), count({$<Student = E({$<Ethnicity = {'Hispanic'}>} Student)>} TOTAL<Subject> DISTINCT Student), count({$<Student = E({$<Ethnicity = {'White'}>} Student)>} TOTAL<Subject> DISTINCT Student) )

Essentially, for every dimension value (Ethnicity), it will create an expression that will include only the records where the Ethnicity is NOT that value. The E() is used to account for students that do no have an Ethnicity assigned to them. And this works! The expression results in the following table (I've colored the cells to indicate the correct value):

However, as soon as you select an Ethnicity:

Because the expression uses the default state, any selection on the Ethnicity field results in a set of no records for the Exclusion. So I tried changing to the 1 state:

pick( match(Ethnicity, $(='''' & concat(DISTINCT Ethnicity,''',''', Ethnicity) & '''')), $(=concat({$<Ethnicity -= {''}>} DISTINCT 'count({1<Student = E({1<Ethnicity = {''' & Ethnicity & '''}>} Student)>} TOTAL<Subject> DISTINCT Student)',',',Ethnicity)) )

And now this works when I make a selection on Ethnicity:

However, when I make a selection on Gender, the number remains the same:

This makes sense, because I am no longer including selections. What I want is for the expression to include the set limited by selections EXCEPT for Ethnicity. I've tried a few different methods but haven't found any success yet. I've attached my sample app to demonstrate this problem.

Any help is appreciated.

Re: Exclude dimension value but don't respect selections in that dimension

dselgo_eidex — Wed, 19 Feb 2020 17:51:41 GMT

So I spent two whole days working on this, and it turns out the correct number was three 😅.

I actually had the answer before posting the question, but I was getting incorrect values. I figured that I could find the results that I wanted by finding the intersection of the default set excluding the selections on Ethnicity and the set of all students not in that ethnicity:

{$<Ethnicity = 1::Ethnicity> * 1<Student = E({1<Ethnicity = {''' & Ethnicity & '''}>} Student)>}

Unfortunately though, this only includes non-null values for Ethnicity, meaning that my results were off. Through trial and error I got to this set expression:

{$<Ethnicity> * 1<Student = E({1<Ethnicity = {''' & Ethnicity & '''}>} Student)>}

This finally worked! It passed all of my tests. However, when I loaded it into my actual app, it was incredibly slow because my actual pivot table has a few aggrs in it. I figured that the intersection of the sets was slowing things down, so I kept experimenting until I got this:

{$<Ethnicity, Student = E({1<Ethnicity = {''' & Ethnicity & '''}>} Student)>}

This created the same results as above, and was much faster to calculate.

I posted the updated app to showcase these findings in case it helps anyone else with a similar problem.

topic Exclude dimension value but don't respect selections in that dimension in App Development

Exclude dimension value but don't respect selections in that dimension

Re: Exclude dimension value but don't respect selections in that dimension