Announcements
You can succeed best and quickest by helping others to succeed. Join the conversation.
cancel
Showing results for
Did you mean:  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  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))  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)) Tags
Community Browser