Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
twanqlik
Creator
Creator

Avg expression differs from Excel

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.

    

EmailAddressRatingModeration_StatusSubmission_Date
hop@test.nl1APPROVED2016-06-29
hop@test.nl2APPROVED2016-06-29
hop@test.nl3APPROVED2016-06-29
test@test.nl2APPROVED2016-06-30
test@test.nl5APPROVED2016-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?

1 Solution

Accepted Solutions
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Try this,

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

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!

View solution in original post

7 Replies
sdmech81
Specialist
Specialist

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
Partner Ambassador/MVP
Partner Ambassador/MVP

Try this,

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

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
vinieme12
Champion III
Champion III

Average – Which average?

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
sunny_talwar

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

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

Capture.PNG

vinieme12
Champion III
Champion III

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)

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
twanqlik
Creator
Creator
Author

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.nl2APPROVED2016-06-30
hop@test.nl2APPROVED2016-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.nl2APPROVED2016-06-30
hop@test.nl2APPROVED2016-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?