topic Re: Avg expression differs from Excel in QlikView

Avg expression differs from Excel

twanqlik — Mon, 06 Feb 2017 11:58:37 GMT

I have a very simple AVG expression:

=avg(distinct{<EmailTypeSent*={'*Rate&Review*'},Moderation_Status*={'APPROVED'}>}Rating)

It should calculate the AVG Rating over all the records that match the EmailTypeSent and Moderation Status. However, i'n my attached .qvw Qlikview shows me an average of 2.75, while it should be 2.6 according to Excel.

EmailAddress	Rating	Moderation_Status	Submission_Date
hop@test.nl	1	APPROVED	2016-06-29
hop@test.nl	2	APPROVED	2016-06-29
hop@test.nl	3	APPROVED	2016-06-29
test@test.nl	2	APPROVED	2016-06-30
test@test.nl	5	APPROVED	2016-06-29

The average of 1, 2, 3, 2 and 5 = 2.6

=AVG(Rating) works in my attached QVW, but will not work in my dashboard because it misses a few criteria.

What did I miss here?

Re: Avg expression differs from Excel

sdmech81 — Mon, 06 Feb 2017 12:11:24 GMT

HI,

Use like:

=avg({<EmailTypeSent={'Rate&Review'},Moderation_Status={'APPROVED'}>}Rating ----->> it ll give u 2.6

Why you are using Distinct over there,any reason(I don't think so)??

Re: Avg expression differs from Excel

kaushiknsolanki — Mon, 06 Feb 2017 12:13:28 GMT

Try this,

=Avg(Aggr(Sum(distinct {<EmailTypeSent*={'*Rate&Review*'},Moderation_Status*={'APPROVED'}>}Rating),Submission_Date,Rating))

Regards,

Kaushik Solanki

Re: Avg expression differs from Excel

kaushiknsolanki — Mon, 06 Feb 2017 12:14:56 GMT

He might be using it because the data has duplicate values.

Regards,

Kaushik Solanki

Re: Avg expression differs from Excel

vinieme12 — Mon, 06 Feb 2017 12:16:11 GMT

Average – Which average?

Re: Avg expression differs from Excel

sunny_talwar — Mon, 06 Feb 2017 12:18:04 GMT

I agree with Sachin.... without distinct seems to be working

=Avg({<EmailTypeSent*={'*Rate&Review*'},Moderation_Status*={'APPROVED'}>} Rating)

Re: Avg expression differs from Excel

vinieme12 — Mon, 06 Feb 2017 12:18:08 GMT

Just removing the distinct keyword also works

=avg({<EmailTypeSent*={'*Rate&Review*'},Moderation_Status*={'APPROVED'}>}Rating)

or you can also use

=sum({<EmailTypeSent*={'*Rate&Review*'},Moderation_Status*={'APPROVED'}>}Rating)/

count({<EmailTypeSent*={'*Rate&Review*'},Moderation_Status*={'APPROVED'}>}Rating)

Re: Avg expression differs from Excel

twanqlik — Mon, 06 Feb 2017 16:06:48 GMT

Indeed without distinct it worked in my test dashboard but not in my full dashboard.

These 2 test record where causing the issue:

test@test.nl	2	APPROVED	2016-06-30
hop@test.nl	2	APPROVED	2016-06-29

The rating is the same for 2 email addresses.

Therefore this formula did work:

=Avg(Aggr(Sum(distinct {<EmailTypeSent*={'*Rate&Review*'},Moderation_Status*={'APPROVED'}>}Rating),Submission_Date,Rating))

However that also didn't work anymore when the submission date and the rating was the same, for example for these 2 records:

test@test.nl	2	APPROVED	2016-06-30
hop@test.nl	2	APPROVED	2016-06-30

Therefore i replaced the submission date with the email address like this:

=Avg(Aggr(Sum(distinct {<EmailTypeSent*={'*Rate&Review*'},Moderation_Status*={'APPROVED'}>}Rating),EmailAddress,Rating))

This formula seems to work now. I'm only trying to understand why it did work

What is the purpose of the value before rating?