topic Re: Average formula in QlikView

Average formula

qlikviewaf — Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Average formula

Anil_Babu_Samineni — Thu, 19 Apr 2018 14:02:19 GMT

Perhaps this?

Sum(Distinct Measure)/Count(MonthField)

Re: Average formula

qlikviewaf — Thu, 19 Apr 2018 14:08:44 GMT

no is not giving the right results

Re: Average formula

Anil_Babu_Samineni — Thu, 19 Apr 2018 14:10:04 GMT

Please provide sample input and may be expected result also helpful. And which object you want to test?

Re: Average formula

qlikviewaf — Thu, 19 Apr 2018 14:20:50 GMT

Find attached

with the selection January and February i should get 1,5 as allocated Instrument average.

Re: Average formula

Anil_Babu_Samineni — Thu, 19 Apr 2018 14:22:52 GMT

It showing section access, Will u remove that and reload() then share. Or provide credentials

Re: Average formula

qlikviewaf — Thu, 19 Apr 2018 14:24:58 GMT

i removed section access

Re: Average formula

Anil_Babu_Samineni — Thu, 19 Apr 2018 14:27:24 GMT

Seems, You forget to reload. TRy to reload and share

Re: Average formula

qlikviewaf — Thu, 19 Apr 2018 14:39:12 GMT

basically i would like to have the AVERAGE of the MONTH AVERAGE

app reloaded

for jan and february the results should be 2

Re: Average formula

Anil_Babu_Samineni — Thu, 19 Apr 2018 14:47:34 GMT

Sorry - I can't help until unless you shared with out access. may be share sample inline table and result set?

Re: Average formula

max_potass — Thu, 19 Apr 2018 14:50:28 GMT

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

Re: Average formula

qlikviewaf — Thu, 19 Apr 2018 15:01:36 GMT

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

Re: Average formula

max_potass — Thu, 19 Apr 2018 15:05:32 GMT

Are Month even relevant?

Sum(Disctinct Column3)/Count(Distinct Column3)

Re: Average formula

Hafedh_BOUMESSOUER — Thu, 19 Apr 2018 15:07:54 GMT

Try this

AVG( Column3)

Re: Average formula

qlikviewaf — Thu, 19 Apr 2018 15:10:43 GMT

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)

Re: Average formula

max_potass — Thu, 19 Apr 2018 15:21:29 GMT

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)

Re: Average formula

qlikviewaf — Thu, 19 Apr 2018 15:24:22 GMT

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

Re: Average formula

max_potass — Thu, 19 Apr 2018 15:38:33 GMT

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.

Re: Average formula

qlikviewaf — Thu, 19 Apr 2018 15:40:47 GMT

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

Re: Average formula

max_potass — Thu, 19 Apr 2018 15:47:22 GMT

I don't understand.