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

[antoniotiman]

Hi Arvind,

maybe this

Sum({<ID={"=Left(ID,1) <> '1'"}>} Value)
or
Sum(If(Left(ID,1) <> 1,Value))

Regards,

Antonio

[arvind_patil]

Thanks for reply , but it will not work in my case

Thanks,

Arvind Patil

[Kushal_Chawda · Accepted solution]

try this

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

[antoniotiman]

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

[neha_shirsath]

Use this-

if(WildMatch(ID,'1*'),Sum({<Plant=-{A}>}Value),sum(Value))

[arvind_patil]

Thanks for reply,

But I dont want to use if condition in it.

Thanks,

Arvind Patil

[Anonymous]

Hi Arvind,

Can you please try this:

sum({<ID-={'1*'}>}Value)

Based on your requirement, i understand that you need sum of value for the ID's other than start with 1.

Hence the output should be :

ID      Plant         Value

200           A                200

400           A                300

400           B                 280

400           C                 220

Result is 1000

[effinty2112]

Hi Arvind,

Maybe:

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

Cheers

Andrew

[rahulpawarb]

May be this:

//First Draft:
=Sum(If(Match(Left(ID,1),1) AND Match(Plant, 'A'), 0, Value))

Regards!

Rahul Pawar