Below function in two different columns

[lukeert19]

Hello all,

Normally in Excel I use the formula ( =if(A3-A2>90;"Yes";"No") where A3 and A2 are date value, as is possible to see I need to works in two different lines, for that reason I have tryed to use the below function without success

 if(num(below(shipping_date))-num(RMA_ReceivingDate)>num(90),'Yes','No')

Who can help me to solve this my problem?

in attached is possible to see one example

thanks in advance 

Luca 

[lukeert19]

Hello someone could help me 😞 ?

[ArnadoSandoval · Accepted solution]

Hi Lukeert19

The simplest way to handle your issue is by using the peek function in your load script to pick the next row shipping date; the script below does just that.

Temp_Data:
LOAD
    RowNo()           as C_Row,  
    Shipping_date,
    RMA_ReceivingDate,
    GDR,
    "90_days",
    RowNo()           as N_Row
FROM [lib://Below_Function/Below_Function.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Foglio1);

NoConcatenate
Shipping_Data:
Load *,
     Peek('Shipping_date', num#(N_Row),'Temp_Data') as Nxt_ShippingDate
Resident Temp_Data 
Order By C_Row; 

Drop Table Temp_Data;

First I load your data to a temporary table, e.g. Temp_Data adding the column RowNo() as N_Row and RowNo() as C_Row

Second I ordered your data by C_Row, while adding the Nxt_ShippingDate with this expression

Peek('Shipping_date', num#(N_Row),'Temp_Data') as Nxt_ShippingDate

Third, the screenshot below shows the table with your data, plus 2 tests columns: RT_Days and Rt_90Days, as well as the Nxt_ShippingDate column, confirming its value is as expected.

The RT_Days column is defined by the expression:

=Nxt_ShippingDate - Shipping_date

The column Rt_90Days is defined by this expression:

=if(Nxt_ShippingDate - Shipping_date > 90, 'Yes', 'No')

Now I did not include the column GDR because your description of the rule was not clear enough, it should not be difficult to add it at your side.

Hope this helps

Arnaldo Sandoval