Set Analysis with zero length / null

[mikelees80]

HI All

I'm trying to write some set analysis to give all the  orders we have that is missing a item division due to incorrect set up

here is my currently set analysis

sum({$<[Item - IH Division]={"=len(trim([Item - IH Division]))=0"}>} [Quantity - Ordered])

I've read multiple threads on here saying this should work example  Blank or NULL in Set Analysis  however i can't get it to work.

I don't want to use an if statement becuase that hits performance

Any ideas?

[Anil_Babu_Samineni]

What is the issue with this expression? Seems okay to me

[sunny_talwar]

May be create a flag in the script

If(Len(Trim([Item - IH Division])) = 0, 1, 0) as Flag

and then this

Sum({$<Flag = {1}>} [Quantity - Ordered])

[mikelees80]

My quantity ordered returns 0

example set analysis i have tried, using an If statment works but not set analysis

Item - IH Division	sum({$<[Item - IH Division]={"=len(trim([Item - IH Division]))>=0"}>} [Quantity - Ordered])	sum([Quantity - Ordered])	sum({$<[Item - IH Division]={"=len(trim([Item - IH Division]))=0"}>} [Quantity - Ordered])	sum(if(len(trim([Item - IH Division]))=0,[Quantity - Ordered],0))	if(len(trim([Item - IH Division]))>0,sum([Quantity - Ordered]),0)
BREAST HEALTH	1,166,294	1,166,294	0	0	1,166,294
DIAGNOSTIC	95,400,804	95,400,804	0	0	95,400,804
FREIGHT	8,398	8,398	0	0	8,398
OTHER	551,589	551,589	0	0	551,589
SKELETAL HEALTH	100,992	100,992	0	0	100,992
SURGICAL	427,281	427,281	0	0	427,281
TBD	344,846	344,846	0	0	344,846
-	0	25,748,176	0	101,471,615	0

[rwunderlich]

It would be useful to see a screenshot of the tablemodel.

-Rob