How to determine when the value of another field equals to the aggregated field

[chaorenzhu]

Hi experts,

Now I have two fields, for simplicity let's call them fieldA and fieldB. See the example below. Both fields have the same distinct values (in this example both type1 and type2).

id	fieldA	fieldB
001	type1	type1
002	type2	type2
003	type1
004	type1
005	type2	type1

My goal is to draw a bar chart, with x-axis being the distinct values in fieldA and y-axis being count in fieldA . Certainly I can use a simple aggregation function aggr(count(id),fieldA). However, because my filter pane is using fieldB as filtering dimension (because fieldB is used to build association with other tables), I would like my x-axis being fieldB. Would appreciate your help on how can I achieve this? Maybe some set analysis using aggr(count({<some set analysis>} id),fieldB)?

The desired output:

fieldA	count
type1	3
type2	2

[chaorenzhu]

I figured it out... it's not possible to aggregate on fieldB and expect my desired output as it will only group by based on values in fieldB. To achieve my expected result I went back to the data model and create a subset table with id and fieldA loaded as fieldB, then I'm able to use fieldB as common field for association