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

Divide cell value based on specific criteria

Mostafa_Anwar
8 - Asteroid

Hi All,

 

I am struggling on how i achieve this, Attached are Sales and Plan dataset and i want to achieve the below:

 - After joining Sales and Plan sheets, I want to divide equally the Plan value based on Sales Rep and month,

So the figure should be like below picture

 

Untitled.jpg

 

Thanks in advance! 

 

Regards,

 

4 REPLIES 4
BrandonB
Alteryx
Alteryx
First add a summarize tool to your sales data and group by rep, group by month, and count the sales column. This will output the rep, month, and number that the plan value needs to be divided by. Then join this to your plan data and configure the join tool to join on rep and month. Now you will have the plan value and count of sales rows in the same data set. Next use a formula tool to divide plan by the count. This will result in the divided count value that you are looking for. Finally, use another join tool to connect this final data set back to your original sales data joined on rep and month and you should have the data set you are looking for!
Mostafa_Anwar
8 - Asteroid

Thanks @BrandonB for your respond, Is it possible to attach the workflow of this solution

BrandonB
Alteryx
Alteryx

Workflow attached!

 

output.png

benakesh
12 - Quasar

Hi @Mostafa_Anwar ,

I am getting different  result  for  one group  . You may need to check your  expected  result  for  this group .  

I used  find and replace. To use find and replace you need  'string  keys'  . 

Process: Create keys for both data sets  , Summarize to get counts for each group and divide plan by count . 

benakesh_0-1572791885243.png

 

Labels