Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Aggregate Data with breaks

sarthak2312
6 - Meteoroid

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 IDProduct NameQuantityStart DateEnd Date
80052390Laptop23/29/20174/22/2017
80052390Laptop45/16/20177/12/2017
20460200Iphone34/23/20177/25/2017
20460200Iphone38/15/20179/15/2017
80052390Laptop48/12/20179/15/2017
31247004Charger53/29/20177/25/2017
20460200Iphone310/25/201712/18/2017

 

I am trying to aggregate data as below:

 

Desired Output
Product IDProduct NameQuantityMin of Start DateMax. of End Date
80052390Laptop63/29/20177/12/2017
20460200Iphone64/23/20179/15/2017
80052390Laptop48/12/20179/15/2017
31247004Charger53/29/20177/25/2017
20460200Iphone310/25/201712/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 🙂

6 REPLIES 6
Thableaus
17 - Castor
17 - Castor

Hi @sarthak2312 

 

Are you grouping by Product ID and Product Name?

 

Cheers,

sarthak2312
6 - Meteoroid

Hi @Thableaus 

 

Yes. I am getting following output. 

 

Product IDProduct NameQuantityMin of Start DateMax. of End Date
20460200Iphone94/23/201712/18/2017
31247004Charger53/29/20177/25/2017
80052390Laptop103/29/20179/15/2017

 

Thanks

cjwaldron18
5 - Atom

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
17 - Castor
17 - Castor

Hi @sarthak2312 

 

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

 

for2.PNG

 

for1.PNG

 

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

 

Cheers,

rafalolbert
ACE Emeritus
ACE Emeritus

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.

 

img.JPG

 

Thanks,

Rafal

 

#Excuse me, do you speak Alteryx?

sarthak2312
6 - Meteoroid

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

Labels