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!

[max_potass]

(Sum({<Month={'January'}>}Distinct Column3)+Sum({<Month={'February'}>}Distinct Column3))/Count(Distinct Month)

This will obviously only work for exactly that data. But what are you trying to do anyway? Is Column three the day or what is that?

If you Just want to ignore the dublicates just use

Sum(Distinct Column3)/Count(Distinct Month)

But then the Values in Column 3 have to unique per month.

[qlikviewaf]

I'm associating to each order line , the "instrument number" which is something needed to generate sales.

Generally speaking the instrument is associated at customer, then the customer using this "instrument" have to generate sales.

the goal is to compare the average instrument that the customer had in a period with the related sales.

Sum(Distinct Column3)/Count(Distinct Month) -> does not work, since if for Jan-feb-march i asssociated to the order always 1 instrument, than the 1 is divided by 3 month = 0,33 ... the resuslts should be 1

[max_potass]

Are Month even relevant?

Sum(Disctinct Column3)/Count(Distinct Column3)

[Hafedh_BOUMESSOUER]

Try this

AVG( Column3)

[qlikviewaf]

yes it's relevant

because if looking 3 months:

Jan month = 1 instruments

Feb month= 3 instruments

Mar month= 3 instrumnets

if i select jan and february the average should be 2, if i select jan feb mar the average should be 2.3333 (7/3month)

[max_potass]

Load Script: (Without order is important)

Load Distinct * Inline

    Month, Instruments

     Jan, 1

     Feb, 3

     Mar, 3;

And then use:

Sum(Instruments)/Count(Distinct Month)

[qlikviewaf]

here a sample of my app, but i'm not able to load with distinct

[max_potass]

See attached.

The problem is, that you can't filter per customer.

I can't help you, I am afraid... You need something like the distinct <Customer>, so it would ignore customer for distinct... But I think, this is not possible.

[qlikviewaf]

but in the month, for the customer the number of insturments cannot change

[max_potass]

I don't understand.