Create KPI field that shows hourly average of record count

How do I create a KPI widget in the QuickSight dashboard that shows hourly average of count of records

e.g. here is my data

event_id return_code
abc123 1
cdf344 0

I would like to show average count of records per hour with return_code 1 as a KPI.

Hi @Karthik1 I am assuming you have another column which is the actual timestamp, correct? If so, write a calculated field that truncates the timestamp to the hour:
truncDate("HH", {your date field})

Then write another calculation to do the sum to the hour, and then take the avg (the sum part is using Level Aware Calculations):
avg(sum(return_code, [hour calc from above]))

@Jesse Thank you for the prompt response. Here is what I did.

  1. added a calculated field “hour_time_script_executed”
    truncDate(“HH”,{time_script_executed})

  2. Added a new calculated field “test_avg_hourly” to do the sum and avg by hour
    avgOver(sum({return_code}), [{hour_script_executed}])

But, after I publish the dataset and pull the “test_avg_hourly” to the visualization, I see " `Table calculation attribute reference(s) are missing in field wells" for KPI metric (not table). Am I missing anything

In your second one you are using avgOver, which is a Table Calculation (unless you use PRE_AGG or PRE_FILTER which turn them into LAC-W calculation) and requires you to use the partition fields to be in your visual. Try just changing it to ‘avg’.

@Jesse If I try that, I see Function AVG should have 1 Argument instead of 2 Arguments error
avg(sum({return_code}), [{hour_script_executed}])

I removed the second argument add I see this
Nesting of aggregate functions like AVG and AVG(SUM(“return_code”)) is not allowed.

your parentheses are in the wrong spot. make sure it looks like I write before:
avg(sum(return_code, [hour calc from above]))

Hi @Jesse Still no luck. Just trying out sum does not work as well.

sum(return_code, [hour_script_executed])

Function SUM should have 1 argument(s) instead of 2 argument(s).

hour_script_executed is of type timestamp (e.g. 2022-09-05T23:00:00.000Z).

As per your aforementioned post, tried

avg(sum(return_code, [hour_script_executed]))

still same error.

Hmm thats strange. Over the summer we released new syntax for the aggregate functions like sum to be able to group them by a dimension, and it’s working for me. See these docs:

Can try this (will need to verify numbers even if it accepts the calc):
avg(sumOver(return_code, [hour_script_executed], PRE_AGG))

@Jesse Ok at least the UI accepts that formula. I see some data. Now,

  1. How do we check if the PRE_AGG sumOver function is giving me the right values ?
  2. How do we troubleshoot why sum({sum}, [{hour_script_executed}])
    giving “Function SUM should have 1 argument(s) instead of 2 argument(s).” error

Hey Karthik - might be easiest to do a screenshare for 5 min to look at this live. Can you send me a DM and we can coordinate a time?