LAC based dimensions

Question & Answer
,
1

Hello everybody,
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.

2

Hello @jacopomstrada !

How are you joining the datasets? Did you use the custom SQL editor to create the join or did you join the table using the console?

Also, to star troubleshooting this I would suggest using a LAC-W function with avg over a partition as well.

3

Hi @jacopomstrada. Since we have not received a response from you on this question, we are going to archive the question. If you still need this question answered, please create a new question topic.