0

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

null
    • Fred
    • 10 mths ago
    • Reported - view

    Can you post your code that you are working with?

      • Digital Humanities Researcher
      • Christoph.2
      • 10 mths ago
      • Reported - view

      Embarrassingly:

      This is the first step:

      let ent := (select entries);
      for i in day(date(ent.created_on)) do
          i
      end
      

      Then, I'd like to check how many records of 'entries' per i.

    • Fred
    • 10 mths ago
    • Reported - view
     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.

      • Digital Humanities Researcher
      • Christoph.2
      • 10 mths ago
      • Reported - view

       creating the day() formula field is easily done, take this field for granted, on the entries table.

       

      The count formula is to be run on a dashboard.

    • Fred
    • 10 mths ago
    • Reported - view
     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?

      • Digital Humanities Researcher
      • Christoph.2
      • 10 mths ago
      • Reported - view

      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.

      • Fred
      • 10 mths ago
      • Reported - view

      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.

    • Digital Humanities Researcher
    • Christoph.2
    • 10 mths ago
    • Reported - view
     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!

     

      • Fred
      • 10 mths ago
      • Reported - view

      I changed the field from sum to average and got:

      Here is the change in the Edit Columns:

      I changed the Name and Footer.

      • Digital Humanities Researcher
      • Christoph.2
      • 10 mths ago
      • Reported - view

      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.

      • Fred
      • 10 mths ago
      • Reported - view
       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.

    • Fred
    • 10 mths ago
    • Reported - view
     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.

      • Digital Humanities Researcher
      • Christoph.2
      • 10 mths ago
      • Reported - view

       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.

Content aside

  • 10 mths agoLast active
  • 13Replies
  • 162Views
  • 2 Following