calculating the weighted average with condition

[sbeaty]

Hello. Thank you in advance for reading this and helping.

I am trying to get the weighted average of the past 7 days of these 3 fields. I have the overall weighted average already, that works. See below.

(sum(FollowUp_ProfessionalRating) + sum(FollowUp_UnderstoodRating) + sum(FollowUp_AddressRating))

/

(count(distinct FollowUpID) * 3)

and I thought this would work below to get it for the past 7 days. it apparently didn't. I don't get anything back besides " - "

if(SurveyTakenDate >= today()-7 and SurveyTakenDate < today(), SUM(FollowUp_ProfessionalRating) + SUM(FollowUp_UnderstoodRating) + SUM(FollowUp_AddressRating))

/

(count( distinct if (SurveyTakenDate >= today()-7 and SurveyTakenDate < today(), FollowUpID) * 3))

What am I doing wrong here??

[vinieme12 · Accepted solution]

you need to filter the period in Set Analysis

(

SUM({<SurveyTakenDate ={">=$(=date(Max(SurveyTakenDate)-7,'YYYY-MM-DD'))<=$(=date(Max(SurveyTakenDate ),'YYYY-MM-DD'))"}>}FollowUp_ProfessionalRating)

+ 

SUM({<SurveyTakenDate ={">=$(=date(Max(SurveyTakenDate)-7,'YYYY-MM-DD'))<=$(=date(Max(SurveyTakenDate ),'YYYY-MM-DD'))"}>}FollowUp_UnderstoodRating)

+ 

SUM({<SurveyTakenDate ={">=$(=date(Max(SurveyTakenDate)-7,'YYYY-MM-DD'))<=$(=date(Max(SurveyTakenDate ),'YYYY-MM-DD'))"}>}FollowUp_AddressRating)

)

/

(count( {<SurveyTakenDate ={">=$(=date(Max(SurveyTakenDate)-7,'YYYY-MM-DD'))<=$(=date(Max(SurveyTakenDate ),'YYYY-MM-DD'))"}>} distinct FollowUpID) * 3))

replace   YYYY-MM-DD with your date format in SurveryTakenDate

refer this for more

https://community.qlik.com/t5/Qlik-Design-Blog/A-Primer-on-Set-Analysis/ba-p/1468344

[sbeaty]

Thank you! This helped!