Filter what to Sum

[#1]

Hello.

I have a source table data like below:

NAME

	TYPE	JAN	FEB	MAR	APR	MAY	JUN	JUL	…
name1	type1	482	488	467	27	205	88	132	…
name1	type2	231	237	216	114	122	130	120	…
name1	type3	0	6	-15	41	26	187	46	…
name2	type1	14	20	-1	194	182	208	74	…
name2	type1	141	147	126	119	116	107	102	…
name2	type1	598	604	583	90	18	45	97	…
name2	type3	239	245	224	57	129	62	163	…
name2	type3	0	6	-15	56	95	75	30	…
name3	type2	7	12	-8	164	231	56	145	…
name3	type2	865	871	850	203	4	202	48	…
name3	type2	-84	-79	-99	67	23	176	215	…

In the import file I definited that first two columns are description.

Now, I have created a table where in one column I listed Names, in other column I summarize months by Names.

How to construct expression to summarize all months only with Type1 and Type2?

[sunny_talwar · Accepted solution]

I would use a The Crosstable Load to transform the data and then do something like this

Sum({<TYPE = {'type1', 'type2'}>}Value)

[#3]

That work's thank you!

What if I had 10 NAMES and I wanted to exclude Name2 for example?
Is there a quick way to exclude or I have to create function to use 9 different names?

[Chanty4u]

try below

add -   symbol before =

Sum({<TYPE -= {'Name2'}>}Value)

[Chanty4u]

or

sum( {$<Type = E({1<Type={'Name2'}>})>} Value)

[sunny_talwar]

Yes, just as Chanty pointed out, it would be as easy as doing this:

Sum({<TYPE = {'type1', 'type2'}, NAME -= {'Name2'}>}Value)

or

Sum({<TYPE = {'type1', 'type2'}, NAME = -{'Name2'}>}Value)

The difference is that 1st expression will narrow down NAME when you select from any of the left over 9 names. Whereas the second option will always display the remaining names no matter what you have selected