Calculated dimension - invalid error message

[blunckc1]

Hi community,

I am have written this nested if statement as a calculated dimension, but I keep getting an invalid dimension error...

=if(sum([CrtEmp.ContractedFTE])=0,'100% vacant',
if(sum([CrtEmp.ContractedFTE])>0 and sum([CrtEmp.ContractedFTE])<PosData.PlannedFTE,'Partially filled',
if(sum([CrtEmp.ContractedFTE])=PosData.PlannedFTE,'Fully filled',
if(sum([CrtEmp.ContractedFTE])>PosData.PlannedFTE,'Over established','LogicEnd'))))

This works perfectly fine when I include it into a table as a measure though, so I don't understand what's going on...

Note the "Occupany Status" is a dimension that is calculated in the load script.  But want to convert this to a calculated dimension so it can dynamically change when a filter that shows who is actively working or not is used.

[rwunderlich · Accepted solution]

You need to wrap that entire expression in an Aggr() that specifies the dimension(s) to use for the Sum() aggregations. For example

Aggr( your if expression, [Position Id])

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[blunckc1]

Total der moment there...  Ha.  Thanks mate!!