problem - unique count of column 1 when column 2 has only 1 entry and = to conditions

[Iain]

working a project to get specific values

 

column one(SVN) has a unique identifier of an entry that has many sub processes

 

column two (VORNR) has the sub processes with values of initial runs and re runs, an initial run would end in 0, a 1st rerun in 1, 2nd in 2 and so on.

the code below allows me to get a count of svn when = to 0 (initial process)

UniqueCount(If(right([VORNR],1)="0",[SVN]))

but this entry may also have multiple reruns if the process fails it must be re ran

i am looking to find out the first time yield by getting a count of SVN like above but also only when there are initial runs and 0 re runs

not sure how i could do this

 

VORNR	SVN
1010	1245
1020	1245
1011	1245
1040	2222
1020	2222

from above the svn 2222 has no re runs as there are no VORNR ending with 1 and only VORNr ending with 0s

 

therefore the count should be 1 

 

help much appriciated

[Olivier Keugue Tadaa]

Hi @Iain

8 hours ago, Iain said:

i am looking to find out the first time yield by getting a count of SVN like above but also only when there are initial runs and 0 re runs

if my understanding is correct, here is what I did it in two steps (maybe it can be simplified)

1. calculate the following two columns:

[runs per SVN]  :::::  UniqueCount(If(right([VORNR],1)="0",[SVN])) over ([SVN])

[reruns per SVN]  :::::  UniqueCount(If(right([VORNR],1)="1",[SVN])) over ([SVN])

2. And then determine the count using this expression:  UniqueCount(If(([runs per SVN]=1) and ([reruns per SVN]=1),[SVN]))