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

How to calculate dynamic weighted average from the data set

Ravy12
7 - 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
5 REPLIES 5
BenMoss
ACE Emeritus
ACE Emeritus

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

Ravy12
7 - Meteor

Thanks Ben, It worked.

 

Regards, Ravi

Ravy12
7 - 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

 

 

BenMoss
ACE Emeritus
ACE Emeritus

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

 

Ben

jkruger
6 - Meteoroid

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

Labels