Avg expression differs from Excel

[twanqlik]

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?

[sdmech81]

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)??

[kaushiknsolanki · Accepted solution]

Try this,

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

Regards,

Kaushik Solanki

[kaushiknsolanki]

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

Regards,

Kaushik Solanki

[vinieme12]

Average – Which average?

[sunny_talwar]

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

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

[vinieme12]

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)

[twanqlik]

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?