8 Replies Latest reply: Oct 29, 2015 3:36 AM by kushal chawda

# how to write a Sum Set analysis isNull condition

Hi all,

I have a set analysis to write something like this.  sum({<menu_type={'M'},isnull(price101)>}sales)

i wanted to write something sum out , if my price101 has Null or zero value.

I hit error when i wrote in this way - sum({<menu_type={'M'},isnull(price101)>}sales)

Rgds,

Jim

• ###### Re: how to write a Sum Set analysis isNull condition

Hi,

Try like this

Hope this helps you.

Regards,

Jagan.

• ###### Re: how to write a Sum Set analysis isNull condition

jagan, I thought of giving him this as solution

Yours is more subtle and efficient, however.

• ###### Re: how to write a Sum Set analysis isNull condition

Nulls are always a bit of a problem in set analysis, You could also try

• ###### Re: how to write a Sum Set analysis isNull condition

ok bro, i will try this.

• ###### Re: how to write a Sum Set analysis isNull condition

I see you want null or zero

=sum({<menu_type={'M'}>} If(isnull(price101) Or price101 = 0, sales))

or try Jagan's suggestion above

• ###### Re: how to write a Sum Set analysis isNull condition

try,

• ###### Re: how to write a Sum Set analysis isNull condition

Handling Null in set analysis is a matter of doubtfulness. Better to convert these nulls into real values (selectable) and use those selectable values in the set analysis.

Some of the solutions provided above, might work and might not too. In fact, I have seen them behaving differently in different qv versions. Only, converted values (in the script) seem to work uniformly across versions.

• ###### Re: how to write a Sum Set analysis isNull condition

I agree with tresesco. You can create a flag in script as below

if(len(trim(price101))<=0,1,0) as Null_Price_flag

Now write set analysis as