flag data using if and peek/ previous function and compare data for each row in a group

[rashmithakur]

Hi All,

I have data which looks like this:

Sub          Visit      Dose

1              1               1.0

1              2                1.0

1              3               0.1

2              1                  1.0

2              2                  2.0

Q. So For Each group of Sub If for Visit 1, Dose<2.0 but for Visits 2  and 3 if the Dose >=2.0 then such sub should be excluded. How can we use the nested if condition with peek/previous function to compare the data and get the output.

Thanks.

[msKarthikeyan]

Hi,

I am assuming you want to show into a table.

if ((aggr (nodistinct sum(if(Visit = 1 and Dose < 2.0 , 1,0)),Sub) = 1) and (aggr (nodistinct sum(if(Visit <> 1 and Dose >= 2.0 , 1,0)),Sub) >= 1) , Sub,Null())

you can uncheck null values to display only the values u want

-Karthik 

[rashmithakur]

Hi Karhtikeyan,

Thanks for your response but the code does not work. Not sure I am missing on any step. 

[msKarthikeyan]

Its working for me for the data yo u shared..

are you displaying this in a table ?  

[rashmithakur]

Hi,

I wanted to run it in the load editor but it is giving me error. I there any other way I can get the output.

I want the records meeting the criteria to be excluded from the table.

Thanks

[msKarthikeyan]

Hi Rashmi,

if you can do it in the charts then you can modify the expression to 

if ((aggr (nodistinct sum(if(Visit = 1 and Dose < 2.0 , 1,0)),Sub) = 1) and (aggr (nodistinct sum(if(Visit <> 1 and Dose >= 2.0 , 1,0)),Sub) >= 1) , Null(),Sub)

and exclude null values in the dimension.  

-Karthik