Aggregate Data with breaks

[sarthak2312]

Hello,

I have searched this site for a while and still can't find the solution to my problem. I was hoping someone here could point me to the right tool/formula. I am trying to aggregate data with breaks when product changes and adds new row. I have data as below:

Available Data
Product ID	Product Name	Quantity	Start Date	End Date
80052390	Laptop	2	3/29/2017	4/22/2017
80052390	Laptop	4	5/16/2017	7/12/2017
20460200	Iphone	3	4/23/2017	7/25/2017
20460200	Iphone	3	8/15/2017	9/15/2017
80052390	Laptop	4	8/12/2017	9/15/2017
31247004	Charger	5	3/29/2017	7/25/2017
20460200	Iphone	3	10/25/2017	12/18/2017

I am trying to aggregate data as below:

Desired Output
Product ID	Product Name	Quantity	Min of Start Date	Max. of End Date
80052390	Laptop	6	3/29/2017	7/12/2017
20460200	Iphone	6	4/23/2017	9/15/2017
80052390	Laptop	4	8/12/2017	9/15/2017
31247004	Charger	5	3/29/2017	7/25/2017
20460200	Iphone	3	10/25/2017	12/18/2017

Here I want to sum up Quantity and have minimum of start date and maximum of end date. I tried summarize tool but it aggregates all the records and doesn't break if the product changes. I would really appreciate someone's push in the right direction. Please let me know if I need to provide any more information. 

Thanks a lot 🙂

[Thableaus]

Hi @sarthak2312 

Are you grouping by Product ID and Product Name?

Cheers,

[sarthak2312]

Hi @Thableaus 

Yes. I am getting following output. 

Product ID	Product Name	Quantity	Min of Start Date	Max. of End Date
20460200	Iphone	9	4/23/2017	12/18/2017
31247004	Charger	5	3/29/2017	7/25/2017
80052390	Laptop	10	3/29/2017	9/15/2017

Thanks

[cjwaldron18]

The Min() function is acting continuously and pulling the minimum value at the field level. If you know the start dates are going to align, might I suggest attempting to group by that field? If not, your going to have to convert your dates from String to Date, filter by date ranges, then union.

Edit: If that does not solve your issue, it would help to provide your workflow and configurations.

[Thableaus · Accepted solution]

Hi @sarthak2312 

You probably need to add a Tile tool to control what you call breaks.

See workflow attached. Leave the "Leave unsorted" box checked in the Tile Tool.

Cheers,

[rafalolbert · Accepted solution]

Hi sarthak2312,

I think this will do the trick: create groups and summarize by groups running vertically and apply the desired aggregations as of sum, min and max.

Thanks,

Rafal

#Excuse me, do you speak Alteryx?

[sarthak2312]

Thank you @Thableaus and @rafalolbert for your quick response. It worked. You guys are awesome. 🙂