Grouping by Formulated Field to show stats from formula

[gilagan]

Hi all I was wondering if any one could assist on how I would group a formulated field and not bring it down as a group by?  When I bring down the formulated field and do a group by, it brings in daily data however I want to group it by another field and have the calculated filed just summarize along with the Group by.

[JohnJPS]

Hi @gilagan,

Not sure I fully understand, but you could try:

1. First select the field you wish to group, just select "group by" for it

2. Next select the field you wish to aggregate, just select "sum" for that one

Done in order like that, the sum will take place within the groups.

Hope that helps; if not, a little more detail about your actual data set and goals would help us help you. :-)

Thanks,

John

[gilagan]

Thank you, I don't have a sum option, the occupancy is a formula so I want to show the details from that formula from the group by of date and site.

[JohnJPS]

You could try a "Group By" on the ""Occupancy" column as well, in which case you would have a listing of each distinct "Occupancy" by the other already established grouping.

[gilagan]

Yes, that's what I'm trying not to do since I want it grouped by site. not daily.

[JohnJPS]

Still not quite following... to remove a daily grouping, couldn't you just remove the "Group By" on Date shown in the screenshot?

[gilagan]

So I am trying to show monthly based occupancy on the date grouping, the date data is daily so I formulated it to show month  So when I group it by date by site, I want to show the calculated occupancy not by day but by month with the group by.  I just need the occupancy to run as is.

So it would show Jan 2017, Site, Occupancy %

[JohnJPS · Accepted solution]

If the original raw data was date, site, and occupancy for that day, I would add a new column (using a formula) to get the month. Assuming occupancy is numeric, I would then group by month and site, with a sum on occupancy.  If you can't sum on occupancy, then for some reason there must be non-numeric data in there somehow: I would analyze the data to understand why, and then work to ensure that it's always numeric (e.g. maybe replace a blank or a space or a null with the number zero, or something along those lines). Hope that helps!