Subtract counts if both condition are met /same Serialnumber

[Applicable88]

Hello,

I have following table:

Serialnumber	Status
1111	B
1111	M
2222	B
2222	M
3333	B
3333	M
5432	B
1234	B

I want to count the serialnumbers with status 'B' . Only in case the serialnumber already exist with status 'M' it should be deducted from the first count.

like that: count({$<Status={B}>}serialnumber)- count({$<Status={M}, (serialnumber of  B= seralnumber of M>}serialnumber) 

Is that better solve with If-Statement? can you give me example in if and in set analysis please. 

I hope I explained my problem well. 

Thank you in advance.

[sunny_talwar]

@Applicable88 So, you only want to count 5432 and 1234 in the example above? Try this may be

Count({$<serialnumber = E({<Status = {'M'}>})>} serialnumber)

[PriyankaShivhare · Accepted solution]

Hi,

I am not sure about your requirement but to get the equal serial no  for different status. u need to whrite this:

serialnumber of  B= seralnumber of M: 

count({<Serialnumber=p({<Status={'B'}>})>*<Serialnumber=p({<Status={'M'}>}) >}Distinct Serialnumber)

and your full expression will be:

count({$<Status={B}>}Serialnumber)- count({$<Status={M},Serialnumber=p({<Status={'B'}>})>*<Serialnumber=p({<Status={'M'}>})>} Distinct Serialnumber)

Hope this helps

Thanks,

Priyanka

[Applicable88]

I want exactly the opposite. I want to count the serialnumbers,  where the serialnumbers exist two times , one with status =B and the other one with status =M.

[Applicable88]

Hello @PriyankaShivhare ,

now I understand. 

Great apporach with the element functions p and e.

Thank you.

[Taoufiq_Zarra · Accepted solution]

@Applicable88  are you looking for ?

=count({<Status={'B'},Serialnumber={"= count({<Status={'M'}>}Status)>0"}>} Serialnumber)

output

[sunny_talwar · Accepted solution]

@Applicable88  try this

Count(DISTINCT {$<serialnumber = {"=Count(DISTINCT {<Status = {'M', 'B'}>} Status) = 2"}>} serialnumber)

[Applicable88]

Hello @Taoufiq_Zarra ,

it works so far very good with the serial numbers that needed to be count. 

In my actual table I also need to do the same thing with the column  "Values" and "Positions" in the same table.

These needs to be summed up not counted.

sum({$<Status={'VER'},Value={"= sum({1<Status={'A'}>}Status)>0"}>} Value)

neither position:

sum({$<Status={'VER'},Position={"= sum({1<Status={'A'}>}Status)>0"}>} Position)

The same structure doesn' t work to sum up these two values. I get way to high numbers. Do you have an idea?

Thanks so far!

[Taoufiq_Zarra]

@Applicable88  you remove 1 in 1<.. (Set analysis) if I understood correctly

or can you share a sample data with values and position and the expected output ?

[Applicable88]

Serialnumber Status Positions Value

1111 B 10 1000

1111 M 10 1000

2222 B 45 8000

2222 M 45 8000

3333 B 70 210

3333 M 70 210

5432 B 40 55

1234 B 25 33