Creating Buckets based on Dimension

[AsmJuv2004]

Hi Everyone,

I have data like this.

Date	US	U	A
1/1/2023	A1	1	AQ1
1/2/2023	A2	1	AQ2
1/3/2023	A3	2	AQ1
1/4/2023	A4	3	AQ2
1/5/2023	A5	4	AQ3
1/6/2023	A6	5	AQ1
1/7/2023	A7	6	AQ2
1/8/2023	A8	7	AQ3
1/9/2023	A9	8	AQ2
1/10/2023	A10	9	AQ1

On taking distinct count of US field for U and A field, I get the below tables

A	Distinct Count of US
AQ1	4
AQ2	4
AQ3	2

U	Distinct Count of US
1	2
2	1
3	1
4	1
5	1
6	1
7	1
8	1
9	1

My end goal is to create buckets based on US counts from above table as shown below. I need to show the below table as a bar chart.

US Buckets	Count of U
0-1	8
>1	1

US Buckets	Count of A
0-1	0
1-2	1
>2	2

Any help would be appreciated. The buckets are pre defined and not dynamic. I'm looking to do this in the front end as we have date filters in the dashboard and the counts need to change based on the filter. I'm ok to do so in script as well if it can achieve the desired result.

[marcus_sommer]

You may apply something like this:

class(aggr(count(distinct U), Dim1, Dim2), 1)

whereby Dim1 and Dim2 are placeholder for the dimensional context in which the calculation should be performed. Instead of class() which creates equally clusters you may also apply n nested if-loops or any kind of pick(match()) to query the count-result and adding the wanted cluster-return.