Can someone explain why the Peek function works when used in this statement?

[#1]

Hi everyone.

I'm using the peek function for a pseudo loop with order dates. Basically, if there has been an order placed within 3 months of the first order, I only want the first order to be displayed. So for example, if a customer had order on 1/01/2015, 02/01/2015, 06/01/2015, and 08/01/2015, only 01/01/2015 and 06/01/2015 would be returned.

I got this to work using this code:

  If(Peek('CUST_ID') = CUST_ID,

  If(AddMonths(ORDER_DATE, -3) > Peek('INDEX_ORDER_DATE'), ORDER_DATE, Peek('INDEX_ORDER_DATE')),

  ORDER_DATE) as INDEX_ORDER_DATE

I'm really happy it works...but I have NO idea as to why it actually works. Can anyone help me? Thanks!

[Gysbert_Wassenaar · Accepted solution]

Well, you have a set of records about orders per customer. Apparently the data is sorted by customer first and order date second.

In the first IF the peek function is used to compare the value of CUST_ID of the current record with that of the previous record. If they are the same that means we're dealing with the same customer so it makes sense to compare order dates. If the records were from different customers then it wouldn't make sense to do something with the order dates. In that case we start anew with the index order date field and use the current value of order date as value for the index order field.

In the second IF we get the order date value of the current record and then calculate the date three months back. That date three months earlier is then compared with the index order date value of the previous record. So we check if the order date lies within 3 months of the previous order date. If it does lie with those three months then we have a hit and use it's value as value for the index order date field. If it isn't then we keep the value of the previous index order date value and use it for the current value of the index order date field.

And so on for each next record.

[sunny_talwar]

I have created the Excel sheet with the same logic as you have above. May be this will make it easier to understand the logic.

[#4]

Thank you for the help. The thing that confuses me a bit is that I'm not sure why I'm able to reference the 'INDEX_ORDER_DATE' in the same statement that I created to define it.

[#5]

Thank you for the help. The thing that confuses me a bit is that I'm not sure why I'm able to reference the'INDEX_ORDER_DATE' in the same statement that I created to define it.

[Gysbert_Wassenaar]

That's because there are two tables involved. The source table where the data is loaded from and the target table where the loaded data ends up. The peek function can access the records already stored in the target table.

[#7]

That makes so much sense now! Thank you!