5 Replies Latest reply on Oct 25, 2016 3:18 AM by Norbert Maijoor

# How to write if condition across multiple rows

Please see the attached the simple packaged workbook

I need help to calculate the "Original Slot Selected" as a Calculated Field as per following logic:

• For a particular Order Id, if the number of Slot_Change=0, then "Original Slot Selected"="Delivery_End_Time"

otherwise, if for a particular Order Id, the number of times slot_change>=1, then  "Original Slot Selected" is equal to the "Value Before" for the first occurence of the Slot_Change.

• I am facing this problem as the same Order ID is coming across multiple rows and multiple slot changes.

Also need help to count the number of Slot_change for a particular Order ID.

• ###### 1. Re: How to write if condition across multiple rows

Find my attempt below and stored in attached workbook version 9.3

• ###### 2. Re: How to write if condition across multiple rows

Hi , Check this calculation once , it may help you .

IF { FIXED [Order id] : SUM( IF [Tag]='SLOT_CHANGE' THEN 1 ELSE 0 END)}=0 THEN STR([Delivery End Time])

ELSE ({ FIXED [Order id]:MIN(IF ISDATE([Value Before]) THEN [Value Before] END)})

END

• ###### 3. Re: How to write if condition across multiple rows

I am unable to open the workbook which you have shared. Kindly email the workbook again at jyothi.bandigi@practo.com

Norbert Maijoor

• ###### 4. Re: How to write if condition across multiple rows

Hi Saijothi,

On which version are you exactly. I am able to download all versions from here

• ###### 5. Re: How to write if condition across multiple rows

Saijyothi,

Here are the steps.

1. LOOKUP: DATETIME(if [Value after]<>"DELIVERED" then [Value after] END)

2. MAX LOOKUP: {fixed [Order id]: max([LOOK UP])}

3. Status: if [Value after]="DELIVERED" then "DELIVERED" else str([max look up])END

4. Latest Status: max([Status])

Hope it helps...