Forum Discussion

DeborahBecker's avatar
DeborahBecker
Qrew Member
4 years ago

Summing data for Calendar Report

Building a Calendar Report - Heat Map.  I want to summarize all the data by day to show the "number of events each day."

For example - Summarize damages by day of the week.  Field name is "Date of Damage"

How do I write the formula to sum the numbers of damages per day?     I will then color-code the day of the week based on the rating of the damages.

Thank you!

------------------------------
Debbie Becker
------------------------------
Calendar
formula queries
reports and charts
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    4 years ago
    This used to be almost impossible or complex. Now it's easy with Formula Queries except for learning the Syntax, which I don't claim to be an expert.

    Maybe this will work

    Size(
    GetFieldValues(
    GetRecords("{6.EX." & [Date] & "}"),3))

    You would replace the 6 with the field ID# of the date of the appointment and replace the [Date] with the field name that holds the date of the appointment.


    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------
    • DeborahBecker's avatar
      DeborahBecker
      Qrew Member
      4 years ago
      Oooh So Close!   It is listing the total of the day - but for each record.  How do I get just one record showing the total for the day?   

      Size(
      GetFieldValues(
      GetRecords("{6.EX." & [Date of Damage] & "}"),3))



      ------------------------------
      Deborah Becker
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        4 years ago
        One way is to use excel to load up a dates table with all the dates for the next 5 years.  Then use this syntax.

        Size(
        GetFieldValues(
        GetRecords("{6.EX." & [Date] & "}","bxxxxxg8"),3))

        where the "bxxxxxg8" is the table ID for your appointments table.

        The other way without an extra table is to write yet another formula of the query so that you can identify the appointment for each day which has the Minimum Record ID.

        You would do that in another field to flag record which are the first appointment of the day.

        Here is some syntax for that.  Field 1 would be a formula multi select text field called [Record ID#s for this Day] 

        GetFieldValues(
        GetRecords("{6.EX." & [Date] & "}"),3)

        Then another field to flag if it's the first one of the day.

        ToNumber(Trim(Left([[Record ID#s for this Day] ],";"))) = [Record ID#]
        //The Formula Query returns a list of all of the Record IDs for this day. The first one on the list (at the left) will be the minimum.

        I know this could be done in one field but this way you get to see the steps in the formula query.


        ------------------------------
        Mark Shnier (YQC)
        mark.shnier@gmail.com
        ------------------------------