Standard deviation of metric average grouped by another field

Q&A
,
1

I have a set of metrics that are aggregated into an average and grouped by a field called {session_id}. Let’s take a metric called {cpu_percent} in this example. I currently have a pivot table that lists the average and stdev of cpu_percent broken out by session_id.

What I want is the standard deviation of the average value of cpu_percent for each session. To see how different each session is from one another.

I have tried using a calculated field with stdevOver using the following code.

stdevOver
(
    avgIf(value, {metric_name}='cpu_percent'),
    [{session_id}]
)

But when I put this in a visual I get nothing. Am I using the right set of functions to accomplish this? If anyone has done something like this and can provide a code sample I’d love to take a look.

Note: cpu_percent is actually not a field, but rather we have a value field that holds the metrics value metric_name field that says which metric it is.

2

Hello @Zach6, welcome to the Quick Sight Community! Maybe try creating a calculated field that just includes an ifelse statement to grab the value you want to use. Something like this:
ifelse({metric_name} = 'cpu_percent', {value}, NULL)

Then use that calculated field in your stdevOver function like this:
stdevOver(avg({calculated value}), [{session_id}], PRE_AGG)

Let me know if that helps!

1 Like
3

So I was able to get this to work as you suggested using the following.

{calculated value}

ifelse({metric_name} = 'cpu_percent', value, NULL)

{std_dev_over_cpu_avgs}

stdevOver(avg({calculated value}), [{session_id}])

@DylanM Now the question is how can I make this generic for any type of metric?

For instance, we have around 50+ metrics that are all different with different metric_name values. This one just happens to be metric_name=cpu_percent. Is there a way that I can write a single set of calulated fields that will do this calculation in a pivot table with all the metrics listed out?

So I don’t have to create like 50 different calculated fields.

4

Hello @Zach6, I believe if you wanted to do it for all metric_names, then you wouldn’t need the ifelse statement at all. You could use stdevOver(avg({value}), [{metric_name}], PRE_AGG) and that should return the standard deviation on each row of the pivot table based on which metric_name it belongs to. I noticed above you are also basing it on session_id, so you could add that in the partition field and it should work how you are expecting. So the final result would look like this:
stdevOver(avg({value}), [{session_id},{metric_name}], PRE_AGG)

Let me know if that works how you expected! If this still has issues, it might be worth building out the standard deviation calculation in your SQL and ingest it as a new column in the dataset. I’ll also link the documentation for the stdevOver function from Quick Sight.

1 Like
5

This is exactly what I needed! Thanks @DylanM!

1 Like