Group by Function
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 gue...
Forum Discussion
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
------------------------------i've tried to use Size(GetRecords()) but it didn't provide different value or it says "Uh-Oh! The way this formula is built will take too long to process. Please try refining your formula. " so i think i might do it wrongly or it will not work in this way , may you drop the way you think it's right to write this formula ,
that's how i wrote it tho :
var text Query="{11.EX.'" & part([Title],1,":") & "'}";size(GetRecords($Query))
------------------------------
Abdelrhman Naser
------------------------------