Should I use a dimension in a calculated field over time or a filter?

Question & Answer
, , ,
1

First time poster. This is new to me. Sorry if I’m not explaining well please let me know if I need to explain another way or better.

I have a clustered bar combo chart that displays my EC2 instances over time. My filters allow me to see how many EC2 instances were running on each day.

x-axis= usage_date
bars= resource_id (count distinct)

I would like to add a KPI visual that shows the the number of instances that were the maximum and the day it occurred. But I can’t seem to figure out what I should use for Value and Trend Group. I think I need to create a calculated field for one or both, but I’m not sure.

When I try to create the calculated field I get ‘ERROR’ as my expression is likely bad. I am not sure if I should be using a filter or a calculated_dimension in my max calculated field or what the calculated field should look like.

It is making me crazy that I can look at the clustered bar combo chart and see which day had the most instances but I can’t figure out how to translate that to the KPI visual. Any suggestions would be appreciated. I’d love it if someone posted a calculated field example for my scenario. Or if you can point me at any links or anything that might help.

Thank you.

2

Hi @brian1

Welcome to the QuickSight community!

You could create a calculated field that calculates the Max count of your resource_id by date.

image
image508×156 3.44 KB

Then you can include the same in your KPI visual. The date of occurence may not be displayed in the KPI visual you may need a separate visual to display that

3

Thank you that’s what I needed! Here’s what gave me the correct number. I only needed one calculated field. I’m posting it in case it helps anyone else.

max(distinctcountOver({resource_id}, [{usage_date}], PRE_AGG))

resource_id= filtered to the resource ids I want to count (in my case, running EC2 instances)
usage_date= filtered to my use case (last 30 days)

Brian

1 Like