Script

[RealEstate2018]

Hi Guys,

I was hoping someone can help me figure out how to modify 2 formulas shown below. Currently, the formula shown        =num(sum(Size) / sum([Maximum Attorney Capacity]), '#,##0')

takes the total square feet of all locations and divides it by the total of all attorneys from every office. Therefore, as an example, Shanghai might have 842 square feet per attorney.

I want to know if there is a way to exclude specific cities, meaning, give me total square of all locations (excluding Beijing and Hong Kong as an example) divided by total attorneys from every office except Beijing and Hong Kong as an example.

The formula is input into a text object so I don't have any way to filter things so the formula needs to be set to exclude directly within the formula.  All locations are identified under a common indicator known as "City" (I've attached a copy from a Bar chart as an example)

This is another formula that I would like to exclude Beijing and Hong Kong as well...= num((sum([Total Number of Attorneys])+sum([Total Non-Attorneys])+sum([Sublet Lawyer Offices]))/sum([Maximum Attorney Capacity]),'###.%');  

This formula gives you a percentage of how well attorney capacity is utilized.  If someone can help me exclude Beijing and Hong Kong, that will be amazing!

[rwunderlich · Accepted solution]

=num(sum({<City-={'Beijing', 'Hong Kong'}>}Size)
/ sum({<City-={'Beijing', 'Hong Kong'}>}[Maximum Attorney Capacity]), '#,##0')

-Rob

[RealEstate2018]

THANKS!! Do you think you can do the same for this formula?

= num((sum([Total Number of Attorneys])+sum([Total Non-Attorneys])+sum([Sublet Lawyer Offices]))/sum([Maximum Attorney Capacity]),'###.%');  

[rwunderlich]

What you need to do is add "set analysis" within your sum() functions. 

{<City-={'Beijing', 'Hong Kong'}>}

Read more about Set Analysis in the help:https://help.qlik.com/en-US/sense/September2019/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/SetA...

This is also a great set of examples: https://community.qlik.com/t5/QlikView-Documents/Set-Analysis-syntaxes-examples/ta-p/1491810

Set Analysis can get complex at times, so don't be afraid to search the forums here for examples or ask questions.  But do give it a try.

-Rob