average of values

[#1]

Hello everyone,

this is most likely an easy solve for you guys,

I have the following formula  COGS = X / Y, and I have MaterialNumber field. each MaterialNumber could have different X and Y values.

such as

MaterialNumber          X               Y              Avg (COGS)

1000                         100             100

1000                         200             210

1000                         500             600    

2000                         650             450

2000                         450             250

I would like to have the Average of COGS for each MaterialNumber

I need to do this in the scirbt.

[abeyphilip]

Hi Mananieal,

By script, do you mean load script?

Then you can try loading "material number" and avg(cogs) separately as:

load MaterialNumber,

avg(x/y) as [Avg (COGS)]

resident XXX

group by MaterialNumber;

And then you can left join this to your main table.

Rgds,

Abey

[#3]

Hey Abey,

Yes, that is the plan, however, loading my report would take about an hour and a half, so what formulas do i have to use? or would it just simply be what you have written ?

I forgot to mention that the MaterialNumber Field is in a different table than X and Y field

Thank you

Regards,

Moaath

[abeyphilip]

Hi Moaath,

You can directly use the load script in my previous post.

Main_tab:

load MaterialNumber,

X,

Y

from xx;

left join MaterialNumber,

avg(x/y) as [Avg (COGS)]

resident Main_tab

group by MaterialNumber;

Regards,

Abey

[jpapador]

If you want to average directly in the chart make MaterialNumber your dimension.  Your expression would be:

Sum(X/Y)/Count(MaterialNumber)