Set analysis with empty values problem

[Anonymous]

Hello,

I use set analysis to filter data. When i try to put a filter in field that contains also empty values in order to exclude 2 values then also the empty values dissapear.

The expression i use is:

sum({< ORDER_StatusDesc= {"CO-Released", "REPLORD-Released"},PRDLI_Desc = {"Die Cut","Die Cut Glued"},MFO_StateDesc -= {"MFO Cancelled","MFO Finished"} >} ORDER_GrossSqm)

In the field MFO_StateDesc i have some records with empty value. When i put in the expression the "MFO_StateDesc -= {"MFO Cancelled","MFO Finished"}"  part then the MFO_Cancelled and MFO_Finished are removed but also the empty ones. I don 't want the empty to be removed.

Can any one help.

[sunny_talwar]

How about this

Sum({<ORDER_StatusDesc= {"CO-Released", "REPLORD-Released"},PRDLI_Desc = {"Die Cut","Die Cut Glued"}, MFO_StateDesc = e({<MFO_StateDesc  = {"MFO Cancelled","MFO Finished"}>})>} ORDER_GrossSqm)

[Anonymous]

Thanks for the reply.

Same effect. The empty values are lost.

[sunny_talwar]

May be convert nulls to white spaces in the script...

If(IsNull(MFO_StateDesc), ' ', MFO_StateDesc) as MFO_StateDesc

Or you can create a new field for the purposes of this set analysis

If(Match(MFO_StateDesc, 'MFO Cancelled', 'MFO Finished'), 0, 1) as Flag

and then use this

Sum({<ORDER_StatusDesc= {"CO-Released", "REPLORD-Released"},PRDLI_Desc = {"Die Cut","Die Cut Glued"}, Flag = {1}>} ORDER_GrossSqm)