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.

How can I group into sets of 4 (create bins) and each set doesn't add up to more than 100?

HeadBanger
8 - Asteroid

I'm trying to group my data into 4 groups.  Week 1 - 4 as this will be the max number of touches I can do per week.

 

HeadBanger_0-1602271648478.png

 

Ideally I would like to randomly group these based Level_3 and Sum_Avg_touch.  With the total of group not to exceed 100 for Sum_Avg_touch.

 

I thought about using tiles but and this works to an extent but I can't group based on the value of the output tile, only shows 1&2 while adding up to 100. 

So maybe i'm not doing something right here.  I'm sure i should use a formula somewhere or some more steps i'm missing.  Can't think much past this on a Friday afternoon. 

 

Thanks for the help in advance and hope my request makes sense.

 

9 REPLIES 9
CharlieS
17 - Castor
17 - Castor

Hi @HeadBanger 

 

I think the Tile tool can still help, but we need to do a little work to prepare. So, if the goal is the have tiles that sum to less than 100, then the number of tiles will be variable, but we can calculate how many tile we'll need. In the example, the sum of the field {Sum_Avg_touch] is about 137, so we'll need 2 tiles to make sure we don't go over 100. If we know the sum of this field (137) we can calculation the number of tiles with CEIL(137/100). Now, this method isn't perfect: if there's an individual value that's greater than 100, then that'll be a problem, and it doesn't guarantee each tile will be less than 100, but it should be helpful in many situations.

 

This method works for this group, but I imagine your group field (Level_3) will have varying contents. We can calculate the number of tiles for each group, but the Tile tool will need to be updated for each group: we can use a batch macro to achieve this. It will batch on each group value (Level_3) and the control parameter can update the number of tiles for that batch. 

 

In the attached workflow I show this suggestion in action. The Summarize tool at the end shows that our number of tiles vary by group and that no tile exceed a sum of 100 value. Check it out and let me know if this works for you. 

 

20201009-BatchTiles.PNG

HeadBanger
8 - Asteroid

Hi Charlie,

 

I think I may have overcomplicated this. 

 

Really all I need is to create a group adding up successive rows by Avg Touch, before that next incremental row adds to 100 , a new group is created. 

I think this can be accomplished regardless of the Level or category.

CharlieS
17 - Castor
17 - Castor

Got it, thanks for that clarification!

 

Ok, two steps here:

1. split values greater that your increment value into individual records. I use a Generate Rows tool for this.

2. Assign groups of the running total into the desired increments. Good news, there's a Running Total tool! However, since the running total needs to reset after every new group assignment, I still used an iterative macro to reset the running total for each group. 

 

20201015-IncrementGroups1.PNG

 

Check out the attached workflow to see how this is done and let me know if you have any questions. 

 

HeadBanger
8 - Asteroid

Hey Charlie,

 

Sorry for not getting back sooner with an update.  This is working as expected but now I see another potential problem.  There could be certain groups that don't make it to 100 and thus we might have some groups much smaller than the rest.  Anyway we can give them an equal distribution?

 

I've somewhat solved this by arranging my group by in Summary Tool but wandering if there were a more fool proof way of doing that?

CharlieS
17 - Castor
17 - Castor

Ah ok, my previous solutions were based on fixed record order and building sequentially, but you're looking for the most equitable groups that don't exceed 100 in any order? I'll think on that one and let you know if I come up with something. 

HeadBanger
8 - Asteroid

You got it Charlie!  Thanks for the effort and assistance so far 🙂 

HeadBanger
8 - Asteroid

Hi Charlie,

 

Was wondering if you could explain how you set up the iterative macro?  I'm trying to duplicate what you did for another column that I also need to make a group for.  Basically, I need to have each set of 4 to be dependent on the values of these 2 columns so that each set not exceed 100 from one column and also not exceed 1.5 M in the other column.  How can we build each set based on the aggregated values from both of these columns? 

 

 
 

 

HeadBanger
8 - Asteroid

Hi @CharlieS 

 

Not sure you got the last message?  I'm trying to recreate your macro but I'm having trouble opening the other file that the macro would use to come up with the running total.  I'm sure this is simple but i'm just not sure how that would look? 

 

Also from my last question, if I do want to have the grouping dependent on 2 columns.  I tried to elaborate on that last thread.  Let me know if you can help with this? 

 

Thanks for your time 🙂

CharlieS
17 - Castor
17 - Castor

Right sorry, been quite busy with work and haven't had much Community time lately. the "Running Total" is a tool that, depending on your version of Designer is either a macro or standard tool. You can drag one down from your toolbar and replace it to get it working. 

Labels