Announcements
cancel
Showing results for
Did you mean:
Creator

## Average formula

Hi,

i have the following data set, lot of order line with different date.

I would like to have the average of column 3, regardless the fact that there are several line.

Basically i would like to select January and February and get as AVERAGE , 3 (2+4/ 2 MONTH).

Which is the right formula to get this result?
Thank you!

25 Replies
Contributor III

Hi Antonio,

I think the formula you are looking is:

=sum(DISTINCT Instruments)/count(DISTINCT myMonth)

In the load statement I have defined myMonth (as the month of the order date). Also see attached files.

Regards

Anders

Creator
Author

Hi,

this is working only if the value of instruments is different month-by-month.

If i set for February and March the same value equal to 4, the average needs to be 4, but appears 2 since the sum(DISTINCT Instruments) should work by month.

How i can i solve this?

Contributor III

I think see the problem with the solution I described before.

If I understand you correctly, I suggest using the following formula instead:

=Avg(Aggr(Avg(DISTINCT Instruments),myMonth))

Regards,

Anders

Creator
Author

i reshaped with

=sum(aggr(sum(DISTINCT Instruments), myMonth))/count(DISTINCT myMonth)

Contributor

Hi Antonio,

you should try ony this formula  : AVG( Column3)

Creator
Author

It's working but only if the customer is selected.

If i don't select the customer the sum(Distinct allocatedinsturment) goes to 0.

In the screenshot below, Allocated instruments is equal to = sum(Distinct AllocatedInstruments) / count(Distinct Month)

do you know why if i deselect the customer (ship-to), the value gos to 0? It's the sum(Distinct allocated Instruments) that goes to 0.

Thank you

Community Browser