If condition in set analysis

[arvind_patil]

Dear Experts,

I have one requirement.

ID      Plant         Value

100           A                  100

200           A                200

100           B                250

400           A                300

400           B                 280

400           C                 220

I required sum of value based on this condition

1. If ID Start with 1 exclude  Plant A.

2. If ID start with other than 1 then Sum Stock

How could i achieve  this in single expression in without any selection.

I Have done some code Please help:

=sum({<ID={'1*'},Plant-={"A"}>+<Plant={"*"}>}Stock)

Thanks ,

Arvind Patil

[Kushal_Chawda]

why you need if condition if same is doable using set analysis? If condition is not advisable due to it's performance impact.

[ankur_abhishek]

=sum(Value)- Sum({<ID={"=Left(ID,1) = '1'"} ,Plant={'A'}>} Value)

[jonathandienst]

You need to exclude ID = 1* from the second clause of your set expression:

     Sum({<ID = {'1*'}, Plant -= {"A"}> + <ID -= {'1*'}, Plant = { "*"}>} Stock)

[kusumanchir]

Hi Arvind,

Please try this,

sum(

{

<ID={'*'}>

-

(<ID={'1*'}>

*

<PLANT={'A'}>)

}

VALUE)

[Harshal_Sanas]

need to use "*" instead of '*'

=sum({<ID={"1*"},Plant-={"A"}>+<ID-={"1*"},Plant={"*"}>}Value)