Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
stascher
Partner - Creator II
Partner - Creator II

Simulating 3 dimensions in bar chart?

Hello,

I'm not sure if this can work, but I've got the following table:

DeptUserAScoreFScore
A1Bob13
A1Sally25
A2Jenn15
A2Marty41
............

Each user will have an AScore (1-5) and an FScore (1-5)

I want to create a stacked bar chart that counts the number of users for each department and score type (A/F), by score value (1-5).

The chart should have Dept & score type on the Y-axis and bar segments that count users by score value for the given Dept & score type.

To simplify things I created a separate table that concats distinct Dept and score type for each existing score value, thereby converting 3 dimensions into 2:

Dept_TypeScore
A1_A1
A1_A2
A1_F3
A1_F5
A2_A1
A2_A4
A2_F1
A2_F5

I was hoping I could create the stacked bar chart with Dept_Type as dimension 1,  Score as dimension 2 and then create a measure that counts users by dept, type and value with an expression like:

if (Right(Dept_Type,1)='A',

count( {<Dept={$(=left(Dept_Type,2))}, AScore=Score>} User),   // count users by AScore

count( {<Dept={$(=left(Dept_Type,2))}, FScore=Score>} User)     // count users by FScore

)

But this doesn't work.  Am I doing something wrong or am I trying to do the impossible?   FYI the reason I don't just count records in the Dept_Type/Score table is that I want the resulting bar chart to be reactive to User and Dept selections.

Thanks for any help,

Steve

Labels (1)
  • Chart

1 Solution

Accepted Solutions
stascher
Partner - Creator II
Partner - Creator II
Author

I figured it out.

=count( aggr(

pick(match(subfield(Dept_Type,'_',2),'A','F'),

   if(Dept=subfield(Dept_Type,'_',1) and AScore=Score,User),

   if(Dept=subfield(Dept_Type,'_',1) and FScore=Score,User)),

User,Score,Dept_Type))

View solution in original post

1 Reply
stascher
Partner - Creator II
Partner - Creator II
Author

I figured it out.

=count( aggr(

pick(match(subfield(Dept_Type,'_',2),'A','F'),

   if(Dept=subfield(Dept_Type,'_',1) and AScore=Score,User),

   if(Dept=subfield(Dept_Type,'_',1) and FScore=Score,User)),

User,Score,Dept_Type))