I've a data at Country and InvoiceDate level and Price of it. I need to find MinPrice in last 60days at each country and date level.
For a Country and InvoiceDate, I need to find the lowest Price in the past 60Days from the current InvoiceDate.
In layman words,
For a country India, if InvoiceDate is 3rd Dec'21, then I'll go back 60 days (which will be 4th Oct'21), so I need have minimum price between these dates.
Solved! Go to Solution.
@Shaaz here you go (solution based on this article: https://community.alteryx.com/t5/Weekly-Challenge/Challenge-106-How-Long-were-the-Lights-On/td-p/14...)
Use a Filter tool on the date (look for the right data type!); use this formula; You could also use this in a Formula tool and then make the output 0 or 1. Then use a new formula which makes the minimum of price when the new field = 1
DateTimeAdd([InvoiceDate], 60, 'days') >= DateTimeToday()
Now you have filtered only the dates which are 60 days before today. (4 oct vs 5 dec is more then 60 days).
Then take a formula tool and create a new field with the min. of price.
Hope this helps you out!