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 ...

Calendar

formula queries

reports and charts

DeborahBecker

Qrew Member

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

Qrew 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
------------------------------

Forum Discussion

Summing data for Calendar Report