Can I apply the MaxOver function to dates up until each order date, and not after?
For example I currently have Customer No, Order No, Item No and Order Date. I want to show the Max Order Date of X Product on every row of data (so I can compare order date to the previous order date of X) but MaxOver is giving me the most recent order date even if it’s after the order date in question, I want the max order date of all orders made before the order date in question.
Hi @Gracebarton ,
You can use the lag function to pick up the previous date on which the item was ordered ahead of current order.
Previous order date for Product
lag({Order Date},[{Order Date} ASC], 1, [Product])
If you want to get the difference between these days, use below calc.
Days since previous order for product
dateDiff({Previous order date for Product},min({Order Date}),'DD')
I’m marking this as solution to your question. Please let us know if you have further questions.
Regards,
Arun Santhosh
Hello, thanks for your response.
Unfortunately this won’t work as I need Previous Order Date of X to appear on every row of data. So in your example, X would be one of the products (e.g., “Support”), and the Previous Order Date of X would appear on every row regardless of that product not being ‘Support’.
See example below, if Product X is ‘Haul of Fame’, the previous order date of X should be as follows (column F). I need something like maxOver(Order Date of X, [customer_no], PRE_AGG) but where Order Date of X is only considering order dates of X prior to the order date of that row, and not after.
Hi @Gracebarton ,
If you need the same order date to show up as previous order date for the very first instance, you can always wrap the previously provided calculation in a coalesce function to fill nulls with current row’s order date. See below.
Previous order date for Product
coalesce(lag({Order Date},[{Order Date} ASC], 1, [Product]), min({Order Date}))
Regards,
Arun Santhosh
