Simulating 3 dimensions in bar chart?

stascher — Sat, 16 Nov 2024 07:38:03 GMT

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

Re: Simulating 3 dimensions in bar chart?

stascher — Tue, 18 Sep 2018 16:20:15 GMT

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

topic Re: Simulating 3 dimensions in bar chart? in App Development

Simulating 3 dimensions in bar chart?

Re: Simulating 3 dimensions in bar chart?