How to use multi level LAC in Quicksight calculated filed and apply it on aggregated value

Q&A
1

Consider the following data:

image
image740×434 43.2 KB

I have a dataset that includes city, store, date, and product_id. I created a calculated field called Available_product, which is defined as: distinct_count({product_id})

Now, I need to compute the average of available products at different levels:

  1. First Level: Calculate the distinct count of product_id at the city, store, and date level.
  2. Second Level: Compute the average of the above distinct count at the city and date level.
  3. Third Level: Compute the average of the second level values at the city level.

I want to display this in a table or pivot table visualization in Quick Sight which would have city and average available products.

Question:

Is there a way to create a calculated field that achieves this aggregation hierarchy in Quick Sight? What would be the best approach to implement this?

2

Hello @nidhi3 , welcome to the Quick Sight community!

I would recommend checking out LAC functions: