topic Re: Use e in set analysis to exclude in App Development

Use e in set analysis to exclude

ishanahiya — Mon, 22 Mar 2021 02:24:43 GMT

This table is from the Data Model.

States	Gender	Population
Totals		4,414,133
South Dakota	Male	270,597
Iowa	Male	1,435,515
Delaware	Male	452,689
South Dakota	Female	281,598
Iowa	Female	1,490,809
Delaware	Female	482,925

This table is from the App Sheet and uses this expression to sum the population.

If([States]='South Dakota', 'Void', Sum({<[States] = {'Iowa' , 'Delaware'},[Gender] -= {'Male'}>}[Population]))

States	Socio-Economic	Population
Totals		1,973,450
South Dakota	Upper	Void
Iowa	Upper	284,117
Delaware	Upper	90,839
South Dakota	Middle	Void
Iowa	Middle	777,583
Delaware	Middle	248,611
South Dakota	Lower	Void
Iowa	Lower	433,652
Delaware	Lower	138,649

I want to exclude Middle from the sum, so the App Sheet tables looks like this when it sums.

States	Socio-Economic	Population
Totals		947,256
South Dakota	Upper	Void
Iowa	Upper	284,117
Delaware	Upper	90,839
South Dakota	Lower	Void
Iowa	Lower	433,652
Delaware	Lower	138,649

When I add this statement to my expression it excludes Middle from the table which is what I want but then it no longer sums.

If [Socio-Economic]=' Middle ',[ Socio-Economic]=E({1<[ Socio-Economic] = {' Middle'}>},

Can you assist?

Re: Use e in set analysis to exclude

Saravanan_Desingh — Mon, 22 Mar 2021 03:34:02 GMT

Try this,

If([States]='South Dakota', 'Void', Sum({<[States] = {'Iowa' , 'Delaware'},[Gender] -= {'Male'},[Socio-Economic]-={'Middle'}>}[Population]))

Re: Use e in set analysis to exclude

ishanahiya — Mon, 22 Mar 2021 15:18:25 GMT

Thanks Saran7de. [Socio-Economic]-={'Middle'} is not removing Middle from the table b/c of the circumstances I described above. i.e. Data Model table vs. App Sheet table.

This syntax will remove Middle from the table but then the expression does not sum without an error.

If [Socio-Economic]=' Middle ',[ Socio-Economic]=E({1<[ Socio-Economic] = {' Middle'}>}

If these two expressions below could be brought together into one expression I think Middle will be removed from the table and the expression will sum without an error.

If([States]='South Dakota', 'Void', Sum({<[States] = {'Iowa' , 'Delaware'},[Gender] -= {'Male'}>}[Population])).

If [Socio-Economic]=' Middle ',[ Socio-Economic]=E({1<[ Socio-Economic] = {' Middle'}>},

That's where I need assistance.

Re: Use e in set analysis to exclude

Saravanan_Desingh — Mon, 22 Mar 2021 23:36:19 GMT

Hello @ishanahiya,

I could not understand your requirement. I am not sure how did you get 284117 for Iowa.

But anyway, try the below.

If([States]='South Dakota', 'Void', Sum({<[States] = {'Iowa' , 'Delaware'},[Gender]-={'Male'},Population=e({<[Socio-Economic]={'Middle'}>})>}[Population]))