Average formula

[qlikviewaf]

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!

[anders_thorngaa]

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

[qlikviewaf]

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?

[anders_thorngaa]

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

[qlikviewaf]

i reshaped with

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

[Hafedh_BOUMESSOUER]

Hi Antonio,

you should try ony this formula  : AVG( Column3)

[qlikviewaf]

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