How to calculate dynamic weighted average from the data set

[Ravy12]

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

Month	Customer code	Mat Code	Vol	Value
201801	AA	CC	200	809.82
201802	AA	CC	100	404.94
201803	AA	CC	13,620	44,793.65
201804	AA	CC	24,000	88,099.69
201805	AA	CC	2,100	7,743.08
201806	AA	CC	17,357	63,634.82
201807	AA	CC	30	40.00
201808	AA	CC	10	20.00
201809	AA	BB	100	200
201810	AA	CC	20	30.00	33.33
201811	AA	BB	50	100.00
201812	AA	BB	200	400	300

[BenMoss · Accepted solution]

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

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

See attached.

Ben

[Ravy12]

Thanks Ben, It worked.

Regards, Ravi

[Ravy12]

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]

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

Ben

[jkruger]

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