Expression to include null values

[#1]

Hi ,

I have below expression which should include both null values and current_R for status_comp_Retail field.But when am using using the below expression it is only displaying current_R values for sttaus_comp_Retail but not null values.Please suggest.

Sum({$<Store_fiscal=(P({<status_comp={Current}>})),status_comp={Current},status_comp_Retail={},status_comp_Retail={Current_R},
status={Current}>} if(Store_fiscal='0099' or Store_Retail='0099' or noncomp_fiscal='0099' or noncomp_retail='0099','0', if(Coupon_Flag='Y' and ACCT='MHOODC' or 'QOP.C' ,aggr(sum(Sold_At*Qty*CONV_RATE),Order_Key)-Coupon_Dollars,
if(Coupon_Flag='Y' and ACCT<>'MHOODC' or 'QOP.C' ,aggr(sum(Sold_At*Qty),Order_Key)-Coupon_Dollars,
if(Coupon_Flag<>'Y' and ACCT='MHOODC' or 'QOP.C' ,(Sold_At*Qty*CONV_RATE),
(Sold_At*Qty))))))

Thanks,

Swetha

[#2]

Hi,

in my experience is not possible to work with null values within set analysis, however you can try the following and see if it works for you:

status_comp_Retail={} + {Current_R}

If the above does not work, you can either use an sum(if) like expression to validate if status_comp_Retail is null or substitute null values within load script for numeric or text flags so you can filter with set analysis.

Regards

[#3]

Thanks Ivan,

I have used the sugestion given by you but its not working.The problem is actually there are no null values in the table so when am selecting bull values it just says 0.I am getting null only when there is no match with other fields in the data model.Please suggest how to overcome this issue.Is there a way in set analysis?

Thanks,

Swetha

[#4]

Hi Everyone,

Is there a way to exclude certain values in set nalysis?Pls suggest

[#5]

Probably I can be more helpful, if you provide us with an example qvw that replicates your issue.

Regards

[#6]

Pls find the enclosed doc which illustrates teh example.

[#7]

Hi Guys,

Any suggestions after seeing the doc.I would have include both the total-5399+39795.Is it possible at all in qlikviw using set analysis.Any help would be appreciated.

Thanks.

[sathishkumar_go]

HI Swetha,

Can u attach ur application here?

Regards

Sathish