Count of records/table per day
Is there a way to count how many entries have the same day in some date field?
Eventually, I need to calculate the average of records over several months, counting only those days that feature in the given date field.
I did not manage to iterate through something like 'my table'.'date field'....
13 replies
-
Can you post your code that you are working with?
-
said:
Then, I'd like to check how many records of 'entries' per i.We all need a nudge in the right direction.
My next question is where is this formula happening? On the 'entries' table? or on a dashboard?
I ask because you originally ask:
said:
Is there a way to count how many entries have the same day in some date field?So where is this "some date field"?
May I suggest you start by adding a field to your entries table that does the day() command of the created_on field. You can also do a month(), year(), and other date commands fields as well. This will help for future data analysis.
-
said:
The count formula is to be run on a dashboard.On your dashboard, I'm guessing you want a table of days of the month in one column and a count in the other?
Or something else?
-
Actually, I'm after an average of entries added per day, but I also want to display the entries/day as a chart. But once I know how to query, I'll manage with these two displays.
-
If you want to do charts then you probably don't need to create any more fields. I don't use charts but was able to create the following very quickly, after watching a Ninox video.
The top one is grouped by days of the month while the bottom shows by days of the week.
This is on a dashboard with a new chart element.
The top has a select formula that points to my Table1. Then I add the following columns:
DayofMonth > a field that is in Table1 that uses the day() command and the data is grouped by this column
Total Transaction > a field that is in Table1 but called Number. It is just a number field and I set this column to be summed.
The bottom chart again points to Table1.
The first column is a formula field that uses:
weekdayName(Date)the second column again uses the Number field (renamed to Total Transactions) and is summed.
You can quickly change it to average as well.
-
-
said:
Total Transaction > a field that is in Table1 but called Number. It is just a number field and I set this column to be summed.Indeed, that was easier than expected. However, I still fail to get the average calculated (neither in the chart nor as formula). I think there should be a function to group in a select statement!
-
I changed the field from sum to average and got:
Here is the change in the Edit Columns:
I changed the Name and Footer.
-
Thanks for your patience!
What is the value in Number in Table1? Previously, I put just 1 in the formula for the chart column that delivers the sum -- worked fine. But the average of this is always 1, of course.My field counter (a number field) is on table1. It wont show the average though.
-
said:
What is the value in Number in Table1?I have various values in the Number field. I also have multiple records that fit each day of Month, otherwise average doesn't work very well.
said:
Well, if I want not just to show, e.g., the average but actually use it's value as a variable in another formula, it would be helpful to get these values beyond the chart.This goes back to my very first post. How do you want to show the data, in a view element or in a formula field.
If you want to do a formula field then you need to figure out how you want to do the day of Month. Are you going to create a formula field for each day of the Month? So you would create a formula field for day 1 then another for day 2 and etc.
Then you can do your filtering for each day and a sum.
sum((select entries where dayofMonth = 1).counter)If you want a view element that is a bit trickier, but can be done.
-
-
said:
I think there should be a function to group in a select statement!I don't quite understand why you would need to group in the select. The current method gets all of the data that you want and then you can create groups based on whatever fields you need.
Content aside
- 2 mths agoLast active
- 13Replies
- 129Views
-
2
Following