I think there is no point in showing the second record as it doesn't have a division associated, its like a full outer join
but if the business asks then we have to show it ...
if you really want you can suppress the second record so that it doesn't come up, as you said in real situation you might be ended up showing multiple records which may not be give any value to the business
or even simpler - you could left join in the Division names to the Data table like this:
LEFT JOIN (Data)
LOAD * INLINE [
Then you could keep your simple Sum(Value_Planned) and Sum(Valule_Actual) and get the two rows you need.
The problem is really in the Divisions table which is inconsistent with the Data (orders) table. So you should either cleanse and fix the Divisions table during the load script phase by adding the Code for the divisions that doesn't exist in the Data (orders) table. This follows normal Business Intelligence and Data Warehouse best practices - which says that you shouldn't have dimension keys in a fact table that hasn't corresponding keys in the dimension table.
Since I mentioned cleansing the dimension Divisions - here is the load script that could be used to create the missing Code keys:
Divisions: LOAD *,Code AS Code_ INLINE [ Code,Division A100,South]; // Make sure to append unknown division codes to the dimension table Divisions: CONCATENATE LOAD Code, '(' & Code & ' unknown)' AS Division RESIDENT Data WHERE Not( Exists( Code_ , Code ) ) ; DROP FIELD Code_;
This is how it will look like:
Notice the (A200 unknown) in the Division column....
Thanks a lot! Sum(Total <Document> Value) did the trick. It provides the same result as my Aggr() solution but is much faster and does not slow down the app! Exactly what i needed! Btw. I never used this syntax, is it something like set analysis? Could you please tell me how is it called or where I could read more about it?
Also thank you for explaining the reason why my table did not work. So when a dimensional value is missing Qlik will not calculate the measures as I would expect it to. I will not change the script of this particular app, but what you posted as suggestion is very interesting and I would use it in future.
Thank you again!
For the syntax and the documentation of TOTAL <field1,field2....> in any aggregate function you can have a look at what is written about Sum() :