Forum Discussion

bottyz's avatar
bottyz
Qrew Trainee
13 hours ago

Counting Instances of String across records for use in a Gauge Chart

Hi all,

I want to use a gauge chart the displays the number of records that contain the letter 'R' as part of a string of in a 'Job Number' field, out of the total number of records in the table.

Typically a job number field value would be in the format of 00000_00, or 00000_A0, or 00000_R00. With R denoting revised.

I've tried using a query report formula, but whilst it's a valid formula attempt, it displays 0. The total number of records for the gauge 

var text QUERY = 
  "{6.EX." & Contains([Job Number], "R") & "}";

Size(
GetRecords($QUERY))

My logic to the above is that its querying the data table, for job number field (ID 6) to see if it contains a string with an 'R' in it, and if so count the total number of true records.

Any ideas?

It would be awesome if the gauge can be coloured to show the revised element in red, but the remainder of the gauge in green. But I really think that's beyond the limits of what is possible. However, if anyone has an idea how this could be accomplished, that would be absolutely appreciated!

formula query

5 Replies

  • KathyQB1's avatar
    KathyQB1
    Qrew Member
    12 hours ago

    To get the count, try changing your query to that below.  You can use the built in CT operator (contains).

    var text QUERY = "{6.CT.'" & "R"  & "'}";

    • bottyz's avatar
      bottyz
      Qrew Trainee
      12 hours ago

      Thanks for the suggestion. Its done something, but not what was intended.

      Out of 73 total records, its now generating a number over 21,000! Maybe its counting the number of characters in the string now instead?


  • KathyQB1's avatar
    KathyQB1
    Qrew Member
    12 hours ago

    I tested in one of my apps and didn't see the behavior you are describing.

    As a simpler solution, can you create the Gauge chart and simply filter where the job number field contains the value R?

    I get the same number with the CT query and filter.

    If you still have an issue, there may be something I'm missing in the set up.

    • bottyz's avatar
      bottyz
      Qrew Trainee
      12 hours ago

      Sounds typical of my luck that it's only happening to me! Thanks for helping anyway! 

      On your other point, if I use it to filter out records (from the 73 total) where the job number contains 'R' (which for the sake of this current dataset would be 3 records), I can't then use the total number of sites as the goal number, as that would also be 3.
      I want to display 3 out of 73 on the gauge. Pre-filtering would show 3 out of 3. Does that make sense?

      Is there a way of printing out on screen, step-by-step how its calculating to 21,000 something, so I can find the root cause?


      • KathyQB1's avatar
        KathyQB1
        Qrew Member
        11 hours ago

        I understand the issue with the filter.  Below is what I did.  Under what does this gauge measure, R Jobs is the count from the query.  I selected averaged as the summary since every record in the table will have the same count.

        I then set the Gauge limits to the number of records in the table.


        This shows 2 of 36 have R in the job code.



        As far as troubleshooting, can you put the calculated field and the job number field in a table report next to each other and see what the results are?  If the query is on the table with the job number, each record should have the same count. I would review the formula results for each record to see if that has the expected value and go from there.