In a table that includes _id and calculated_date, each time a new calculation is made in the table new record is inserted with _id and calculated_date.
I want to find the average calculation performed for each _id for all the data and also by day.
Welcome @Muhammad_Irbaz_Siddi
did you check out the avgOver function?
BR
yes, I have checked it out but in my case, I have only _id and calculation_date, I don’t have any numeric column to take the average.
I have to make a calculation that how many average calculations are performed on a single _id in a day.
I have tried also count() and then avg() functions but it said “Nesting of aggregate functions like AVG and AVG(COUNT(”_id)) is not allowed.
can you share some sample data?
Sure, Here’s the data:
The staff_id can appear more than once in a day or an hour.
So you want to show the avg per day for each ID?
I want to show like this for example:
_id: 72181 has 4 calcs performed (calculation_date) on 13 December 2022
_d: 72181 has 4 calcs performed (calculation_date) on 14 December 2022
_id: 23091 has 4 calcs performed (calculation_date) on 15 December 2022
The average per day would show 4 calcs per _id.
Hi @Muhammad_Irbaz_Siddi -
Did you solve your calculation issue? You can use a count function grouped by date and id and then an avg function grouped by date. You can see an example in our documentation here on using grouping dimensions with functions.