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.
added a calculated field “hour_time_script_executed”
truncDate(“HH”,{time_script_executed})
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’.
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))