Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.