Forum Discussion

JohnRomano's avatar
JohnRomano
Qrew Cadet
15 days ago
Solved

Running Total

I want to get a running total of the number of rows in a report. I have a formula that is COUNT([field name]), which returns a value of 1. To get the running total value, do I have to first create a var text query on the field I am counting?

var text QUERY //var text QUERY = "{fid#.}";

Is the Running Total formula: SumValues(GetRecords($QUERY),fid#)

reports
  • MarkShnier__You's avatar
    MarkShnier__You
    13 days ago

    A way to do this without needing a Formula Query is to make use of a summary report which will break on every record.  Include a formula numeric field called count with a formula of 1, and then show it on the report and set it to show as a running total.

    But if that does not work for you, then we would need to know the sort and filters on the report to help with a Formula Query.

  • bfultz's avatar
    bfultz
    Qrew Member
    14 days ago

    If you want to sum the total value of a specific field (i.e. the total price of all combined orders) then you would use SumValues like you have above. However, if you're simply wanting a total number of records (i.e. the total number of orders), then you can use the Size() function along with a GetRecords query that selects all records like this:

    Size(GetRecords("{3.XCT.null}"))

    The query there is simply selecting any record that doesn't contain (XCT="doesn't contain") a null value in fid #3, which is the default Record ID field.

     

    The COUNT formula counts the number of arguments given to it, which is why your formula is returning 1.

    • MarkShnier__You's avatar
      MarkShnier__You
      Icon for Qrew Legend rankQrew Legend
      13 days ago

      A way to do this without needing a Formula Query is to make use of a summary report which will break on every record.  Include a formula numeric field called count with a formula of 1, and then show it on the report and set it to show as a running total.

      But if that does not work for you, then we would need to know the sort and filters on the report to help with a Formula Query.