cancel
Showing results for
Did you mean:

Alteryx designer Discussions

#SANTALYTICS

Gather all 9 clues to complete the final Weekly Challenge on Dec 16!

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

 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
Highlighted
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.

See attached.

Ben

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

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

Ben

Meteoroid

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

Labels