Understanding Join and Concatenate (Rob Wunderlich)
Ive been reading an excellent article by Rob on Join and concatenate and decided to code it up just to be clear how joins are affecting some of my calculations that just dont look right but that i couldnt diagnose (couldnt see the wood for the trees. and this example looked pretty simple.)
However coding it up raised some further questions, in the attached QVw I dont understand why some expressions (highlightd red dont calculate correctly) as you can see from the File i have tried several different Loading Methods.
Could Someone have a look at the QVW and explain please?
I dont think I would have got into my problems if SalesAmount-BudgetAmount didnt return Null
Let me put this in SQL terms, assuming you're more familiar with SQL, and maybe it will make it more clear what is happening.
SalesAmount - BudgetAmount:
SELECT B.Year ,B.Region ,S.SalesAmount - B.BudgetAmount as Result FROM Budget B, Sales S WHERE S.Year = B.Year AND S.Region = B.Region GROUP BY B.Year, B.Region
This will return an error in SQL. You MUST use an aggregation function on the result since the two involved fields are not in your group by. QlikView doesn't treat the chart equivalent as an error, and instead returns null unless there is only one department for a given year and region, but it's the same idea - you're doing a group by without doing aggregation. Not a good idea. In fact, it's such a bad idea that I'm not sure why QlikView even allows it, but it does.
sum(BudgetAmount - SalesAmount):
SELECT B.Year ,B.Region ,sum(B.BudgetAmount - S.SalesAmount) as Result FROM Budget B, Sales S WHERE S.Year = B.Year AND S.Region = B.Region GROUP BY B.Year, B.Region
This IS valid SQL. But before it can do the sum, it has to join your two tables together. When it does, it will create a temporary table with B.Year, B.Region, B.BudgetAmount and S.SalesAmount. When it does that, it's going to REPEAT the budget data for as many Departments as you have for the Year and Region. That's just how joins work.
Year Region BudgetAmount Department SalesAmount 2008 East 1500 Sports 1490 2008 East 1500 Toys 1200 2008 West 10000 Clothes 150 2008 West 10000 Sports 3000 2008 West 10000 Toys 4000 2009 East 1000 Sports 500 2009 East 1000 Toys 480 2009 West 6000 Sports 200 2009 West 6000 Toys 300
It will then do the group by and sum on THIS table. QlikView is effectively doing the same thing. Therfore, for 2008 East, for instance, it calculates (1500-1490)+(1500-1200)=310. It's what you told it to do. It's just not what you wanted.
To get what you wanted, you need to do sum(BudgetAmount)-sum(SalesAmount), or BudgetAmount - sum(SalesAmount):
SELECT Year ,Region ,BudgetAmount - SumSalesAmount as Result FROM ( SELECT Year ,Region ,BudgetAmount FROM Budget UNION ALL SELECT Year ,Region ,sum(SalesAmount) as SumSalesAmount FROM Sales GROUP BY Year, Region) Temp GROUP BY Year, Region
Hopefully that would work in SQL. In any case, what you're doing here is telling it to handle the totals from the two tables separately, and only combine them together once all of the SalesAmounts have been summed across the Departments.
SELECT B.Year ,B.Region ,sum(S.SalesAmount) - sum(B.BudgetAmount) as Result FROM Budget B, Sales S WHERE S.Year = B.Year AND S.Region = B.Region GROUP BY B.Year, B.Region
Would also fail to give the correct answer in SQL so how does the next expression Budget-Sales work where budget and sales are just alias's for sum operations across the same data ?
Both Budget-Sales and sum(BudgetAmount)-sum(SalesAmount) should behave the same way. In each case, QlikView computes the two sums independently, THEN subtracts. The two tables are never joined the way you show in your SQL. It calculates the one sum using the one table, then the other sum using the other table, basically like I showed in my last SQL.
