Find date where maximum value occurred

[smagaldi]

Hi community, 

Anyone can help me find the date where maximum sales occurred? And hopefully how long it took for it to happen? 

For example: 

date	product	revenue
01/01/2020	1	100
01/01/2020	2	150
01/03/2020	3	120
01/03/2020	2	140
01/03/2020	1	200
01/10/2020	2	150
01/10/2020	1	300
01/11/2020	2	120
01/15/2020	1	130

I'm trying to find: 

- Max revenue of 1 - $ 300

- Day of max revenue of 1 - 01/10/2020

- How long it took for the product 1 to have the biggest revenue - 9 days 

Having a final output for all the rows, knowing their peak sales and how long it took for each of them to reach their maximum sales in a day. 

Any help? 😄 

[Luke_C · Accepted solution]

Hi @smagaldi 

Would something like this work? 

1. Convert date to alteryx date format
2. Sort data by revenue descending 
3. Sample the first record for each product (representing the max)
4. Join this to the minimum date for each product (from the summarize tool)
5. DateTimeDiff formula to get the difference between the date of the max revenue and the first date the product appears.

[smagaldi]

Hi Luke, 

That was super helpful, thanks!