How to aggregate and create a pie chart based on item classification in QuickSight?

Question & Answer
,
1

Hello everyone,

I’m new to Quicksight and please forgive me if my question sounds naive.
I have a table where each row represents a record. The first column is the ID of an item, and each item has a corresponding time with different values. I need to classify the items into three classes. For example, if an item’s corresponding time is in the past and its average value is less than 50, it belongs to the ‘RED’ class. If the average value is greater than or equal to 50, it belongs to the ‘YELLOW’ class. If the corresponding time is in the future, it belongs to the ‘GREEN’ class.

Now, I want to aggregate this table and create a pie chart that represents the number of items in each class. How should I do this? I tried using a calculated field, but it does not work for pie chart plotting.

image
image696×585 13.3 KB

Your help is greatly appreciated! Thank you =)

2

Hello and welcome to the community,

Can you help me understand a little more detail on your question.

Is the value column already an average, or are you looking to first aggregate the data and then classify?

If looking to aggregate first, then will the time for an item always be the same, for example what if you had some item A records with a date before now and some after now?

Let me know and I can provide some guidance on the calculated fields.

Thanks
Rob

3

Hi @robkc , thank you so much for your reply!
The value column needs to first aggregate then classify, and the time should always be same for the same item, so we don’t need to worry about the difference of date.

4

Hi @Albert

Have you looking into PRE_AGG calculations?

ifelse({date}<now() AND avgOver({value},[],PRE_AGG)<50,'RED',etc...)