Hi everyone, I am receiving an error when creating a bar graph visual "custom aggregation field is not allowed as dimension.’
I want to group a field called “UTC” into buckets “0-5” and “5-20” which is the group count (on the X-axis) and show the count on the Y-Axis.
Group formula used for UTC count bucketing: ifelse({UTC}<6,‘0-5’,‘5-20’)
UTC formula is: distinct_countIf({field1},{date}<‘2024-02-07’ OR {date}>‘2024-01-29’)
Would appreciate your help on how I can aggregate this formula to show the count in different buckets.
@Rmo-1 currently custom aggregations are not allowed as dimension and hence the error. It would be helpful to understand a bit more about your use case, few sample rows of the data including columns involved to find an alternate solution approach.
Thanks @DeepakS,
I attached a screenshot on how I would like to preview the data in a chart where I have the group ranges that groups “UTC” values into buckets (0, ‘1-5’, ‘6-10’) on the x-axis and the count of UTC on the Y-Axis.
Note that UTC is a calculated field that counts {field1} during a certain period as follows: distinct_countIf({field1},{date}<‘2024-02-07’ AND {date}>‘2024-01-29’)
And Range Group is: ifelse({UTC} = 0, ‘0’, ({UTC} < 6 AND {UTC} >0 ), ‘1-5’, ‘6-10’)
@Rmo-1 You have couple of options for this use case
1/ Move the distinct conditional count UTC to the database layer and then use the aggregated table as source in QuickSight
2/ In order to achieve this within QuickSight, follow steps below
a/ create a calculated field that will select attribute1 if it is within date range criteria else null as below - ‘date_range_attribute1’
ifelse (date< parsedate(“2024-01-26”,“YYYY-MM-dd”) AND date>parsedate(“2024-01-10”,“YYYY-MM-dd”),attribute1,“”)
b/create a filter to exclude null values and apply that to the visual. This will ensure that only those attributes are considered which fall within the specified date range
c/ Using the newly created column in step #a, create another calculated field to count the attributes by users as - distinctCountOver({Date_range_attribute1},[User],PRE_AGG)
d/Then create the grouping of above distinct count as below
ifelse({Countover_user_ifelse_flag}<2,“0-1”,{Countover_user_ifelse_flag}<4,“2-3”,
“4+”)
