Simple calculation in load script not creating any values

[thomasmercer]

I'm loading a table to pre-summarise then just trying to find the difference between the two so I can pull data from a text bank. I've put together the below loading script which doesn't error however if I use [Avg Score Are] or [Avg Score Want] in my dashboard I get results as expected, but [Avg Diff] doesn't display any results at all.

I think I'm missing something obvious but been stuck on this one for a while!

[PreSummarise]:
LOAD
[Company Name],
[QI],
[Where we],
Avg([Question Score]) AS [Avg Score Are],
Sum([Question Score]) AS [Total Score Are]
Resident EVP.Diagnostic.Survey.TestData1
where [Where we] = 'Are'
and [Company Name] = '$(vCompanyName)'
group by
[Company Name],
[QI],
[Where we];

JOIN
LOAD
[Company Name],
[QI],
[Where we],
Avg([Question Score]) AS [Avg Score Want],
Sum([Question Score]) AS [Total Score Want]
//Sum([Question Score]) - [Total Score Are] AS [Total Dif]
Resident EVP.Diagnostic.Survey.TestData1
where [Where we] = 'Want'
and [Company Name] = '$(vCompanyName)'
group by
[Company Name],
[QI],
[Where we];


[Summarise]:
LOAD	
[Company Name],
[QI],
[Where we],
[Avg Score Want]-[Avg Score Are] as [Avg Diff] //I don't know why this doesn't work
Resident PreSummarise

[sunny_talwar · Accepted solution]

When you join the two tables, remove the join on Where We field because they have different values... the two Averages won't align against each other

[PreSummarise]:
LOAD
[Company Name],
[QI],
[Where we],
Avg([Question Score]) AS [Avg Score Are],
Sum([Question Score]) AS [Total Score Are]
Resident EVP.Diagnostic.Survey.TestData1
where [Where we] = 'Are'
and [Company Name] = '$(vCompanyName)'
group by
[Company Name],
[QI],
[Where we];

JOIN
LOAD
[Company Name],
[QI],
[Where we],
Avg([Question Score]) AS [Avg Score Want],
Sum([Question Score]) AS [Total Score Want]
//Sum([Question Score]) - [Total Score Are] AS [Total Dif]
Resident EVP.Diagnostic.Survey.TestData1
where [Where we] = 'Want'
and [Company Name] = '$(vCompanyName)'
group by
[Company Name],
[QI],
[Where we];


[Summarise]:
LOAD	
[Company Name],
[QI],
[Where we],
[Avg Score Want]-[Avg Score Are] as [Avg Diff] //I don't know why this doesn't work
Resident PreSummarise;

[thomasmercer]

Excellent! Works a treat. Thanks Stalwar1