Simulating 3 dimensions in bar chart?

[stascher]

Hello,

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

Dept	User	AScore	FScore
A1	Bob	1	3
A1	Sally	2	5
A2	Jenn	1	5
A2	Marty	4	1
...	...	...	...

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_Type	Score
A1_A	1
A1_A	2
A1_F	3
A1_F	5
A2_A	1
A2_A	4
A2_F	1
A2_F	5

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

[stascher · Accepted solution]

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))