Custom aggregation field not allowed as a dimension

Question & Answer
,
1

Hi Team,

Custom aggregation field not allowed as a dimension:
What is the reason customer aggregation field is not allowed as a dimension in Quicksight?

Thank you!

2

Hello @QuickSightRokie !

My assumption is that dimension fields are generally used for categorizing qualitative data in BI tools and the Quicksight team followed that line of thinking. So, if you have an aggregated field or metric you would generally only apply that to a quantitative field well.

Depending on your use case there might be a good work around, what are you trying to accomplish in your visual?

3

Thank you so much for your reply @duncan!

I have calculated Metric1 as below
Metric1 = (x-y) / y

Now,I am trying to use the custom aggregation (Metric1) as dimension (Metric1 >= 70%).

Is there a workaround for this?

Thank you!

4

Hey @QuickSightRokie !

If I understand correctly you want to group by anything over 70% so I would recommend to try using an ifelse statement. Something like this:

ifelse(
{Metric1} >= .7,
{your_qualitative_field},
NULL
)
5

Hey @QuickSightRokie !

Were you able to try the calculation I mentioned above?

6

Yes, thank you so much!

1 Like
7

Hello everybody,

I’m facing a similar problem, I need to use a custom aggregated field as a dimension, I had to create that using the approach explained in the use case 4 of this post: Create advanced insights using level-aware calculations in Amazon QuickSight | AWS Business Intelligence Blog. I’m in the situation where I have duplicated values because of the join of two datasets with a different granularity.

I use this formula to get my average value:

avg(max({dp_value},[{part_number},{supplier_sml_code},{plant_code},{dt_week}]))

and then this one to create a dimension based on the previous:

ifelse({dp_value_lac} <= 0.70,'1','2')

but the result, when I try to create a heatmap with the dimension as row, is the following

image
image1533×652 12.4 KB

I think the cause is that the average is a custom aggregation field, but I’m afraid there’s no other way of calculating a meaningful average of duplicated values if not like that.