Tableau - Calculating average where date is less than value from another data source

Question

I am trying to calculate the average of a column in Tableau, except the problem is I am trying to use a single date value (based on filter) from another data source to only calculate the average where the exam date is <= the filtered date value from the other source.

Note: Parameters will not work for me here since new date values are being added constantly to the set.

I have tried many different approaches, but the simplest was trying to use a calculated field that pulls in the filtered exam date from the other data source.

It successfully can pull the filtered date, but the formula does not work as expected. 2 versions of the calculation are below:

IF DATE(ATTR([Exam Date])) <= DATE(ATTR([Averages (Tableau Test Scores)].[Updated])) THEN AVG([Raw Score]) END

IF DATEDIFF('day', DATE(ATTR([Exam Date])), DATE(ATTR([Averages (Tableau Test Scores)].[Updated]))) > 1 THEN AVG([Raw Score]) END

Basically, I am looking for the equivalent of this in SQL Server:

SELECT AVG([Raw Score]) WHERE ExamDate <= (Filtered Exam Date) 

Below a workbook that shows an example of what I am trying to accomplish. Currently, it returns all blanks, likely due to the many-to-one comparison I am trying to use in my calculation.

Any feedback is greatly appreciated!

Tableau Test Exam Workbook 

Answer 1

• You will be able  to solve this by using Custom SQL to join the tables together and calculate the average based on my conditions, to get the column results I wanted.

• It would still be great to have this ability directly in Tableau, but whatever gets the job done.

SELECT 

[AcademicYear]

,[Discipline]

--Get the number of student takers

,COUNT([Id]) AS [Students (N)]

--Get the average of the Raw Score

,CAST(AVG(RawScore) AS DECIMAL(10,2)) AS [School Mean]

--Get the number of failures based on an "adjusted score" column

,COUNT([AdjustedScore] < 70 THEN 1 END) AS [School Failures]

--This is the column used as the cutoff point for including scores

,[Average_Update].[Updated]

FROM [dbo].[Average] [Average]

FULL OUTER JOIN [dbo].[Average_Update] [Average_Update] ON ([Average_Update].[Id] = [Average].UpdateDateId)

--The meat of joining data for accurate calculations

FULL OUTER JOIN (

SELECT DISTINCT S.[Id], S.[LastName], S.[FirstName], S.[ExamDate], S.[RawScoreStandard], S.[RawScorePercent], S.[AdjustedScore], S.[Subject], P.[Id] AS PeriodId

FROM [StudentScore] S

FULL OUTER JOIN

(

--Get only the 1st attempt

SELECT DISTINCT [NBOMEId], S2.[Subject], MIN([ExamDate]) AS ExamDate

FROM [StudentScore] S2

GROUP BY [NBOMEId],S2.[Subject]

) B

ON S.[NBOMEId] = B.[NBOMEId] AND S.[Subject] = B.[Subject] AND S.[ExamDate] = B.[ExamDate]

--Group in "Exam Periods" based on the list of periods w/ start & end dates in another table.

FULL OUTER JOIN [ExamPeriod] P 

ON  S.[ExamDate] = P.PeriodStart AND S.[ExamDate] <= P.PeriodEnd

WHERE S.[Subject] = B.[Subject]

GROUP BY P.[Id], S.[Subject], S.[ExamDate], S.[RawScoreStandard], S.[RawScorePercent], S.[AdjustedScore], S.[NBOMEId], S.[NBOMELastName], S.[NBOMEFirstName], S.[SecondYrTake]) [StudentScore]  

ON 

([StudentScore].PeriodId = [Average_Update].ExamPeriodId 

AND [StudentScore].Subject = [Average].Subject 

AND [StudentScore].[ExamDate] <= [Average_Update].[Updated])

--End meat

--Joins to pull in relevant data for normalized tables

FULL OUTER JOIN [dbo].[Student] [Student] ON ([StudentScore].[NBOMEId] = [Student].[NBOMEId])

INNER JOIN [dbo].[ExamPeriod] [ExamPeriod] ON ([Average_Update].ExamPeriodId = [ExamPeriod].[Id])

INNER JOIN [dbo].[AcademicYear] [AcademicYear] ON ([ExamPeriod].[AcademicYearId] = [AcademicYear].[Id])

--This will pull only the latest update entry for every academic year.

WHERE [Updated] IN (

SELECT DISTINCT MAX([Updated]) AS MaxDate

FROM [Average_Update]

GROUP BY[ExamPeriodId])

GROUP BY [AcademicYear].[AcademicYearText], [Average].[Subject], [Average_Update].[Updated], 

ORDER BY [AcademicYear].[AcademicYearText], [Average_Update].[Updated], [Average].[Subject]

Get certified in this field to make your career. Enroll in Tableau Certification now.