Need Formulas to Bucket Property Values

[AReyes22]

Hi!

I am completely lost by the Formula syntax within HubSpot, but want to achieve the following:

In reports, display the following high-level buckets if state and industry properties =

Return:
Northeast: CT, ME, MA, NH, NJ, NY, PA, RI, VT
Southeast: AL, AR, FL, GA, KY, LA, MS, NC, SC, TN, VA, WV
Midwest: IL, IN, IA, KS, MI, MN, MO, NE, ND, OH, SD, WI
West: AZ, CO, ID, MT, NV, NM, UT, WY
Pacific: AK, CA, HI, OR, WA

Return:

Professional Services: Accounting, Alternative Dispute Resolution, Architecture & Planning, Business Supplies and Equipment, Civic & Social Organization, Civil Engineering, Commercial Real Estate, Design, Executive Office, Facilities Services, Financial Services, Fund-Raising, Government Relations, Graphic Design, Human Resources, Industrial Automation, Information Services, Insurance, Investment Banking, Investment Management, Judiciary, Law Enforcement, Law Practice, Legal Services, Legislative Office, Management Consulting, Market Research, Marketing and Advertising, Non-Profit Organization Management, Outsourcing/Offshoring, Philanthropy, Photography, Political Organization, Professional Training & Coaching, Program Development, Public Policy, Public Relations and Communications, Public Safety, Real Estate, Research, Security and Investigations, Staffing and Recruiting, Think Tanks, Translation and Localization, Venture Capital & Private Equity, Writing and Editing

Technology & Software: Animation, Computer & Network Security, Computer Games, Computer Hardware, Computer Networking, Computer Software, Internet, Electrical/Electronic Manufacturing, Information Technology and Services, Mobile Games, Nanotechnology, Online Media, Semiconductors, Telecommunications, Wireless

Healthcare & Wellness: Alternative Medicine, Biotechnology, Dairy, Health, Wellness and Fitness, Hospital & Health Care, Medical Devices, Medical Practice, Mental Health Care, Pharmaceuticals, Veterinary

Education & Training: Education Management, E-Learning, Higher Education, Individual & Family Services, Libraries, Primary/Secondary Education, Professional Training & Coaching

Facilities & Maintenance Services: Building Materials, Construction, Environmental Services, Farming, Fishery, Food Production, Glass, Ceramics & Concrete, Industrial Automation, Machinery, Packaging and Containers, Paper & Forest Products, Plastics, Printing, Renewables & Environment, Utilities, Warehousing

Logistics & Transportation: Airlines/Aviation, Aviation & Aerospace, Automotive, Defense & Space, Import and Export, Logistics and Supply Chain, Maritime, Package/Freight Delivery, Railroad Manufacture, Shipbuilding, Transportation/Trucking/Railroad

Consumer & Retail Services: Apparel & Fashion, Arts and Crafts, Consumer Electronics, Consumer Goods, Consumer Services, Cosmetics, Entertainment, Events Services, Fine Art, Food & Beverages, Furniture, Gambling & Casinos, Hospitality, Leisure, Travel & Tourism, Luxury Goods & Jewelry, Music, Museums and Institutions, Newspapers, Performing Arts, Restaurants, Retail, Recreational Facilities and Services, Sporting Goods, Sports, Supermarkets, Textiles, Tobacco, Wine and Spirits

Other / Not Classified: Chemicals, Government Administration, Mining & Metals, Oil & Energy, Ranching, Religious Institutions

[karstenkoehler · Accepted solution]

Hi @AReyes22,

Currently, you need to nest if statements - which makes for quite bulky formulas:

IF([COMPANY.hs_state_code]="CT" OR [COMPANY.hs_state_code]="ME","Northeast",
  IF([COMPANY.hs_state_code]="AL" OR [COMPANY.hs_state_code]="AR","Southeast",
  IF([COMPANY.hs_state_code]="IL" OR [COMPANY.hs_state_code]="IN","Midwest","")))

I haven't created the entire formula but you get the idea. Check if company state code is CT or if company state code is ME or (continue with all others), if yes, return Northeast, if no, go one level "deeper" and check if company state code is AL etc. If after the "deepest level" nothing applies, return empty (see the last "" before the brackets close).

The same logic would apply to your industries. You can probably use the snippet above to leverage gen AI and create the entire formula for you.

Best regards!

[karstenkoehler · Accepted solution]

@AReyes22 the x-axis should have the count of companies, not the distinct count of the calculation field ( = how many distinct values there are: 5)

[karstenkoehler · Accepted solution]

Hi @AReyes22,

Currently, you need to nest if statements - which makes for quite bulky formulas:

IF([COMPANY.hs_state_code]="CT" OR [COMPANY.hs_state_code]="ME","Northeast",
  IF([COMPANY.hs_state_code]="AL" OR [COMPANY.hs_state_code]="AR","Southeast",
  IF([COMPANY.hs_state_code]="IL" OR [COMPANY.hs_state_code]="IN","Midwest","")))

I haven't created the entire formula but you get the idea. Check if company state code is CT or if company state code is ME or (continue with all others), if yes, return Northeast, if no, go one level "deeper" and check if company state code is AL etc. If after the "deepest level" nothing applies, return empty (see the last "" before the brackets close).

The same logic would apply to your industries. You can probably use the snippet above to leverage gen AI and create the entire formula for you.

Best regards!

[AReyes22]

I have a custom Deal property "state_region" that syncs the State from the associated Company.

This is what I'm getting back from the formula (below):

and

AI Formula:

IF(
[DEAL.state_region]="CT" OR [DEAL.state_region]="ME" OR [DEAL.state_region]="MA" OR
[DEAL.state_region]="NH" OR [DEAL.state_region]="NJ" OR [DEAL.state_region]="NY" OR
[DEAL.state_region]="PA" OR [DEAL.state_region]="RI" OR [DEAL.state_region]="VT",
"Northeast",

IF(
[DEAL.state_region]="AL" OR [DEAL.state_region]="AR" OR [DEAL.state_region]="FL" OR
[DEAL.state_region]="GA" OR [DEAL.state_region]="KY" OR [DEAL.state_region]="LA" OR
[DEAL.state_region]="MS" OR [DEAL.state_region]="NC" OR [DEAL.state_region]="SC" OR
[DEAL.state_region]="TN" OR [DEAL.state_region]="VA" OR [DEAL.state_region]="WV",
"Southeast",

IF(
[DEAL.state_region]="IL" OR [DEAL.state_region]="IN" OR [DEAL.state_region]="IA" OR
[DEAL.state_region]="KS" OR [DEAL.state_region]="MI" OR [DEAL.state_region]="MN" OR
[DEAL.state_region]="MO" OR [DEAL.state_region]="NE" OR [DEAL.state_region]="ND" OR
[DEAL.state_region]="OH" OR [DEAL.state_region]="SD" OR [DEAL.state_region]="WI",
"Midwest",

IF(
[DEAL.state_region]="AZ" OR [DEAL.state_region]="CO" OR [DEAL.state_region]="ID" OR
[DEAL.state_region]="MT" OR [DEAL.state_region]="NV" OR [DEAL.state_region]="NM" OR
[DEAL.state_region]="UT" OR [DEAL.state_region]="WY",
"West",

IF(
[DEAL.state_region]="AK" OR [DEAL.state_region]="CA" OR [DEAL.state_region]="HI" OR
[DEAL.state_region]="OR" OR [DEAL.state_region]="WA",
"Pacific",

""
)))))

What am I doing wrong?

[karstenkoehler · Accepted solution]

@AReyes22 the x-axis should have the count of companies, not the distinct count of the calculation field ( = how many distinct values there are: 5)

[AReyes22]

Thank you! That worked. Now, I created a formula to bucket Industry. This is the formula:

IF(
[COMPANY.industry]="Accounting" OR
[COMPANY.industry]="Alternative Dispute Resolution" OR
[COMPANY.industry]="Architecture & Planning" OR
[COMPANY.industry]="Business Supplies and Equipment" OR
[COMPANY.industry]="Civic & Social Organization" OR
[COMPANY.industry]="Civil Engineering" OR
[COMPANY.industry]="Commercial Real Estate" OR
[COMPANY.industry]="Design" OR
[COMPANY.industry]="Executive Office" OR
[COMPANY.industry]="Facilities Services" OR
[COMPANY.industry]="Financial Services" OR
[COMPANY.industry]="Fund-Raising" OR
[COMPANY.industry]="Government Relations" OR
[COMPANY.industry]="Graphic Design" OR
[COMPANY.industry]="Human Resources" OR
[COMPANY.industry]="Industrial Automation" OR
[COMPANY.industry]="Information Services" OR
[COMPANY.industry]="Insurance" OR
[COMPANY.industry]="Investment Banking" OR
[COMPANY.industry]="Investment Management" OR
[COMPANY.industry]="Judiciary" OR
[COMPANY.industry]="Law Enforcement" OR
[COMPANY.industry]="Law Practice" OR
[COMPANY.industry]="Legal Services" OR
[COMPANY.industry]="Legislative Office" OR
[COMPANY.industry]="Management Consulting" OR
[COMPANY.industry]="Market Research" OR
[COMPANY.industry]="Marketing and Advertising" OR
[COMPANY.industry]="Non-Profit Organization Management" OR
[COMPANY.industry]="Outsourcing/Offshoring" OR
[COMPANY.industry]="Philanthropy" OR
[COMPANY.industry]="Photography" OR
[COMPANY.industry]="Political Organization" OR
[COMPANY.industry]="Professional Training & Coaching" OR
[COMPANY.industry]="Program Development" OR
[COMPANY.industry]="Public Policy" OR
[COMPANY.industry]="Public Relations and Communications" OR
[COMPANY.industry]="Public Safety" OR
[COMPANY.industry]="Real Estate" OR
[COMPANY.industry]="Research" OR
[COMPANY.industry]="Security and Investigations" OR
[COMPANY.industry]="Staffing and Recruiting" OR
[COMPANY.industry]="Think Tanks" OR
[COMPANY.industry]="Translation and Localization" OR
[COMPANY.industry]="Venture Capital & Private Equity" OR
[COMPANY.industry]="Writing and Editing",
"Professional Services",

IF(
[COMPANY.industry]="Animation" OR
[COMPANY.industry]="Computer & Network Security" OR
[COMPANY.industry]="Computer Games" OR
[COMPANY.industry]="Computer Hardware" OR
[COMPANY.industry]="Computer Networking" OR
[COMPANY.industry]="Computer Software" OR
[COMPANY.industry]="Internet" OR
[COMPANY.industry]="Electrical/Electronic Manufacturing" OR
[COMPANY.industry]="Information Technology and Services" OR
[COMPANY.industry]="Mobile Games" OR
[COMPANY.industry]="Nanotechnology" OR
[COMPANY.industry]="Online Media" OR
[COMPANY.industry]="Semiconductors" OR
[COMPANY.industry]="Telecommunications" OR
[COMPANY.industry]="Wireless",
"Technology & Software",

IF(
[COMPANY.industry]="Alternative Medicine" OR
[COMPANY.industry]="Biotechnology" OR
[COMPANY.industry]="Dairy" OR
[COMPANY.industry]="Health, Wellness and Fitness" OR
[COMPANY.industry]="Hospital & Health Care" OR
[COMPANY.industry]="Medical Devices" OR
[COMPANY.industry]="Medical Practice" OR
[COMPANY.industry]="Mental Health Care" OR
[COMPANY.industry]="Pharmaceuticals" OR
[COMPANY.industry]="Veterinary",
"Healthcare & Wellness",

IF(
[COMPANY.industry]="Education Management" OR
[COMPANY.industry]="E-Learning" OR
[COMPANY.industry]="Higher Education" OR
[COMPANY.industry]="Individual & Family Services" OR
[COMPANY.industry]="Libraries" OR
[COMPANY.industry]="Primary/Secondary Education" OR
[COMPANY.industry]="Professional Training & Coaching",
"Education & Training",

IF(
[COMPANY.industry]="Building Materials" OR
[COMPANY.industry]="Construction" OR
[COMPANY.industry]="Environmental Services" OR
[COMPANY.industry]="Farming" OR
[COMPANY.industry]="Fishery" OR
[COMPANY.industry]="Food Production" OR
[COMPANY.industry]="Glass, Ceramics & Concrete" OR
[COMPANY.industry]="Industrial Automation" OR
[COMPANY.industry]="Machinery" OR
[COMPANY.industry]="Packaging and Containers" OR
[COMPANY.industry]="Paper & Forest Products" OR
[COMPANY.industry]="Plastics" OR
[COMPANY.industry]="Printing" OR
[COMPANY.industry]="Renewables & Environment" OR
[COMPANY.industry]="Utilities" OR
[COMPANY.industry]="Warehousing",
"Facilities & Maintenance Services",

IF(
[COMPANY.industry]="Airlines/Aviation" OR
[COMPANY.industry]="Aviation & Aerospace" OR
[COMPANY.industry]="Automotive" OR
[COMPANY.industry]="Defense & Space" OR
[COMPANY.industry]="Import and Export" OR
[COMPANY.industry]="Logistics and Supply Chain" OR
[COMPANY.industry]="Maritime" OR
[COMPANY.industry]="Package/Freight Delivery" OR
[COMPANY.industry]="Railroad Manufacture" OR
[COMPANY.industry]="Shipbuilding" OR
[COMPANY.industry]="Transportation/Trucking/Railroad",
"Logistics & Transportation",

IF(
[COMPANY.industry]="Apparel & Fashion" OR
[COMPANY.industry]="Arts and Crafts" OR
[COMPANY.industry]="Consumer Electronics" OR
[COMPANY.industry]="Consumer Goods" OR
[COMPANY.industry]="Consumer Services" OR
[COMPANY.industry]="Cosmetics" OR
[COMPANY.industry]="Entertainment" OR
[COMPANY.industry]="Events Services" OR
[COMPANY.industry]="Fine Art" OR
[COMPANY.industry]="Food & Beverages" OR
[COMPANY.industry]="Furniture" OR
[COMPANY.industry]="Gambling & Casinos" OR
[COMPANY.industry]="Hospitality" OR
[COMPANY.industry]="Leisure, Travel & Tourism" OR
[COMPANY.industry]="Luxury Goods & Jewelry" OR
[COMPANY.industry]="Music" OR
[COMPANY.industry]="Museums and Institutions" OR
[COMPANY.industry]="Newspapers" OR
[COMPANY.industry]="Performing Arts" OR
[COMPANY.industry]="Restaurants" OR
[COMPANY.industry]="Retail" OR
[COMPANY.industry]="Recreational Facilities and Services" OR
[COMPANY.industry]="Sporting Goods" OR
[COMPANY.industry]="Sports" OR
[COMPANY.industry]="Supermarkets" OR
[COMPANY.industry]="Textiles" OR
[COMPANY.industry]="Tobacco" OR
[COMPANY.industry]="Wine and Spirits",
"Consumer & Retail Services",

"Other / Not Classified"
)))))))

But I keep getting this: (it is a cross object report, but I tried it as a single object report and got the same thing)

[karstenkoehler · Accepted solution]

@AReyes22 this is often a notice when what you're trying to do is simply too much for HubSpot / the custom report builder. You can verify by shortening it and checking if it's working after removing sections. If yes, then the custom report is not powerful enough to handle this. You could flag it with HubSpot support but I doubt there's an immediate fix.

As a workaround, you could use a workflow with branches to assign a property value.

[SamTassey]

Hi @AReyes22 

Welcome to the Community! I understand you're looking for guidance on creating formulas for your reporting needs. I think these Knowledge Base articles  will be helpful, as they provide an overview of our formula functions and syntax!

In case you still need some further assistance here, I'm going to loop in a few of our experts! 

@d-lupo, @BarryGrennan, and @KarstenG