Constructing custom table columns

[pervak]

Hi,
I have a dataset with Customers metrics (like Sales, Units, etc).
I want to create a table with comparison of specific to customer data (picked by Customer ID), show the MIN, MAX, AVG columns for the corresponding metrics of cohort (also picked by filter “Customer Band”, “Region” etc), and then calculate the difference in between MIN and Customer’s metrics and AVG and Customer’s metrics.

Appreciate your ideas on how to make this with the regular means of Quicksight in SPICE databases. I can get customer data filtered by Customer ID, but how to add 5 other calculated columns?

The result should be something like:

Metrics name | Seller metrics | MIN | MAX | AVG | DIFF MIN | DIFF AVG
metrics 1
metrics 2
metrics 3
…

I’ve tried this approach: Inserting dataset parameters into custom SQL - Amazon QuickSight . I was able to construct the Custom SQL to get the dataset parameter and prepare all data as 6 rows. But it is very unstable, as there are millions of rows in the dataset, and it’s often just not working because of timeout (didn’t find the way of increasing timeout as well).

Appreciate any ideas.

[Brett]

Hi @pervak and welcome to the Quick Sight community!
You can build calculated fields to handle these additional columns; here’s a great article that dives deeper in to Level Aware calculations that can assist in building the required calculations:

Level-Aware Calculations: Tips and Best Practices

[Level-Aware Calculations in Amazon Quick Sight] Level-Aware Calculations (LAC) are powerful functions that allow us to perform calculations at a granularity level which is different from the granularity of our datasets and/or visuals. The sample dataset referenced by this article is a dataset of product orders placed by customers. Each order can contain multiple products and each product in an order has a unique Line Item ID. Each row of our dataset represents one line item but we want to pe…

[pervak]

Thanks, @Brett ! I will check this way and give the feedback.

[Brett]

Hi @pervak,

Following up here as it’s been awhile since we last heard from you on this thread; did you have any additional questions regarding your initial post?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you

[pervak]

Hi @Brett , thanks for asking. Still in implementation phase.

[Brett]

Hi @pervak,

As this topic has been idle for some time now, I’ll go ahead and close out this topic. However, if you have any additional questions regarding this, please feel free to create a new post in the community and link this discussion for relevant information.

Thank you!