community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

How to calculate dynamic weighted average from the data set

Meteor

Hello Experts,

 

I have a data set for the entire year 2018 and from that data set I would like to calculate the weighted average for last three months dynamically at a 'customer code' and 'material code' level.

 

It is just one output that needs to be calculated at 'customer code' and 'material code' level from the data set.

 

See below an example: How can I achieve in my alteryx workflow ?

 

Two outputs from the below data set

 

  1. Weighted Average for 'AA + CC' = 33.33 = ( (40*30 + 20*10 + 30*20) / (30 +10 +20))
  2. Weighted Average for 'AA & BB' =  300 = ((200*100 + 100*50 +400*200)/ (100 +50+200))

 

MonthCustomer codeMat CodeVolValue 
201801AACC200809.82 
201802AACC100404.94 
201803AACC13,62044,793.65 
201804AACC24,00088,099.69 
201805AACC2,1007,743.08 
201806AACC17,35763,634.82 
201807AACC3040.00 
201808AACC1020.00 
201809AABB100200 
201810AACC2030.0033.33
201811AABB50100.00 
201812AABB200400300
Alteryx Certified Partner
Alteryx Certified Partner

This is easily achievable using the multi-field formula tool.

 

The checking of 'Customer code' and 'Mat Code' is the key here.

 

2019-02-01_14-34-03.png

 

See attached.

 

Ben

Highlighted
Meteor

Thanks Ben, It worked.

 

Regards, Ravi

Meteor

Hi Ben,

 

Another question related to this.

 

How can I put this as a filter in workflow so that only line items for end user code and material code for the last three months are filtered.

 

For eg:

 

From below data, for customer AA and Mat code as BB, line items for fiscal period for 201809, 201811, 201812 should be filtered out.

 

Regards, Ravi

 

 

Alteryx Certified Partner
Alteryx Certified Partner

Hi @Ravy12 , you have not posted any sample data.

 

Ben

Meteoroid

Create a filter where [Weighted Average] IS NOT NULL for TRUE

Labels