Set Analysis/ Expression :How to include a value irrespective of the selection ?

[ajipaul03]

Hi Experts ,

I have a unique requirement to do certain analysis.

Here is the table structure

VALUE_CAT	OPTION	AMT
R&M	R&M	-$100
Cost	A	-$200
Cost	A	-$50
Cost	B	-$400
Cost	C	-$600
Revenue	A	$250
Revenue	B	$300
Revenue	B	$450
Revenue	C	$650

I need to write an expression Sum({$<......>}AMT), so that when user makes a selection in the option field, it should  always  include 'R&M'  VALUE_CAT.

ex.

OPTION A selection

VALUE_CAT	Total
R&M	-$100
Cost	-$250
Revenue	$250

OPTION-B Selection

VALUE_CAT	Total
R&M	-$100
Cost	-$400
Revenue	$750

Can someone please help?

I thought of duplicating the R&M for A,B and C OPTIONs  but I thought some experts would be able to guide me 🙂

Thanks,

Aji

[Taoufiq_Zarra]

@ajipaul03  One solution:

=sum({1<VALUE_CAT={'R&M'}>}AMT)+sum({$<VALUE_CAT={"*"}-{'R&M'}>} AMT)

output:

A selected

B selected

[Kushal_Chawda · Accepted solution]

@ajipaul03  try below

=sum({<OPTION,VALUE_CAT={'R&M'}>+<VALUE_CAT={"*"}>}AMT)

[ajipaul03]

Thank you Taoufiq  and Kush