Hi All , I've been trying to do group by as a formula due to I'm trying to create Gauge Chart that count title that has been added to field [Title] , when i use count() it count all records , i guess what i need to group by first all titles that has same name then count those groups of titles , in order to give me how many titles we have at the end Thanks in Advance 🤗------------------------------Abdelrhman Naser------------------------------

Are you trying to count the 'Distinct' count of Titles? So the number of unique entries? The distinct count is something that you can leverage in relationships if you have a setup that would allow you to summarize your title data somehow. ------------------------------Chayce Duncan------------------------------

Yes more or less , the title entered as long title , starts with title name then season then EPs and so on , so i need first part of the title only so i used Formula " Part([Title], 1, ":") " now i need to group the result of this formula then count , or count "Distinct " the results as you mentioned . ------------------------------Abdelrhman Naser------------------------------

If you have the title broken out into it's own field that you can summarize on, you can summarize them all into a 'Master' table if you have one, essentially a table with a single record that relates to ALL of your data records in the table you have the Title field in. Then the native relationship allows you to add a summary field of the 'Distinct Count' of a value in that child table. This would get you the Unique # of fields that you could report on. ------------------------------Chayce Duncan------------------------------

Thank you , Chayce but is there a way to do this in Report Formulas without creating a new table ? ------------------------------Abdelrhman Naser------------------------------

You could try and get really creative with formula queries and a combination of the Size(GetRecords()) queries but I don't recommend it. Without doing it for real, you would essentially be creating a field that finds the first occurrence in your data set where that title appears and making a number value that flags that specific one as 1, and all of the other entries as 0 and then counting that field in your report. At the end of the day you probably do want to get the # of unique entries somewhere else anyway otherwise reporting might be a little funky. For example if you have a table for lets say employees like so Title Name Project Manager A Project Coordinator B Project Manager C And you wanted the count of unique Titles for that table - then you're essentially making a field that is just going to repeat itself Title Name Unique Titles Project Manager A 2 Project Coordinator B 2 Project Manager C 2 The idea is that you want to move that value of unique titles somewhere where you can actually report/show that there are only 2 unique values in the data set being evaluated. ------------------------------Chayce Duncan------------------------------

Group by Function | Qrew Discussions

13 Replies

ChayceDuncan
Qrew Captain
10 months ago
Are you trying to count the 'Distinct' count of Titles? So the number of unique entries? The distinct count is something that you can leverage in relationships if you have a setup that would allow you to summarize your title data somehow.

------------------------------
Chayce Duncan
------------------------------
- AbdelrhmanNaser
  Qrew Member
  10 months ago
  Yes more or less , the title entered as long title , starts with title name then season then EPs and so on , so i need first part of the title only so i used Formula " Part([Title], 1, ":") "
  
  now i need to group the result of this formula then count , or count "Distinct " the results as you mentioned .
  
  ------------------------------
  Abdelrhman Naser
  ------------------------------
  - ChayceDuncan
    Qrew Captain
    10 months ago
    If you have the title broken out into it's own field that you can summarize on, you can summarize them all into a 'Master' table if you have one, essentially a table with a single record that relates to ALL of your data records in the table you have the Title field in. Then the native relationship allows you to add a summary field of the 'Distinct Count' of a value in that child table. This would get you the Unique # of fields that you could report on.
    
    ------------------------------
    Chayce Duncan
    ------------------------------

Forum Discussion

Group by Function