14 Replies Latest reply: Apr 26, 2011 9:45 AM by Sridhar Ethiraj

# Problem with expression where field is blank

Hi,

I have a nar chart with 2 expressions. The first expression shows me the average number of "days since last movement" on the account where the "Transaction Amount" Field is not blank. The second expression should show me the exact same thing, but where the Transaction Amount field IS blank. The first one works 100%, but the second expression returns nothing. Can someone see what is wrong with my second expression?

Expression 1: Avg({1<[Trans Amount]-={} >} [Days since last movement])

Expression 2: Avg({1<[Trans Amount]={} >} [Days since last movement])

Thanks,

G

• ###### Problem with expression where field is blank

if(isnull(YourField),'Y','N') as NullFlag

Avg({<NullFlag={'Y"}>} [Days since last movement])

Regards,

Dinesh.

• ###### Problem with expression where field is blank

I'm not sure where I would put that in, as there techically are no blanks in this field...

What I do is load an excel spreadsheet with account numbers, then I load daily transaction extracts with the following WHERE clause:

WHERE ([Tran Code]=35 or [Tran Code]=36) and exists([Account No])

So only transactions where the account numbers match an account number in the very first list will be loaded.

So the "Trans Amount" field will only be blank if this person has not yet made a purchase, i.e. he is not in the transaction extracts.

Did I confuse the matter now?

• ###### Problem with expression where field is blank

Hi,

KIndly try inserting only single quotes like ''

• ###### Problem with expression where field is blank

Hi Gaurav,

I have tried both single and double quotes, with and without spaces between them - nothing seems to work...

G

• ###### Problem with expression where field is blank

Gerhardl - Have you tried using the Null() function within your second expression?

• ###### Problem with expression where field is blank

Hi DV,

No - please could you show me how? On this expression

Avg({\$<[Trans Amount]={} >} [Days since last movement])

Thanks,

G

• ###### Problem with expression where field is blank

Hi,

Check this below Expression helps you.

Avg({<[Days since last movement] = {"=len([Trans Amount)=0"}>} [Days since last movement])

Untested expression.

- Sridhar

• ###### Problem with expression where field is blank

No I'm afraid that also returns nothing...

• ###### Problem with expression where field is blank

Hi,

Look at the attached application.

If this also doesn't solve the your problem. post the sample data with your required output.

- Sridhar

• ###### Problem with expression where field is blank

For handling nulls the best thing u can do is to get the length.

U can try this:

If(Len(TransInYearMonth<0 ),1,0) as NullFlag

and use this in your expression:

-1 <NullFlag= {"1"}>

Thanks,

Srihari

• ###### Problem with expression where field is blank

Alternatively, can i not exclude a certain selection?

So I would say

Avg( [Days since last movement]) but EXCLUDE where [Trans Amount]-={}

How can I do that???

Thanks,

G

• ###### Problem with expression where field is blank
Hi, How about using len() function? You can try this: avg( {1} [Days since last movement]) ) Hope this helps
• ###### Problem with expression where field is blank

I managed to figure it out.

I created a bookmark where all accounts with a transaction are selected. Then I exclude the bookmark in my expression:

Avg ({1-Bookmark01} [Days since last movement])

Thanks for everyone's input. What a mission that was.

Regards,

G

• ###### Problem with expression where field is blank

Hi,

Try like this way

Normal 0 false false false EN-US X-NONE X-NONE

Normal 0 false false false EN-US X-NONE X-NONE

Avg({\$<[Trans Amount]={''=len(Trans Amount)=0"} >} [Days since last movement])

Normal 0 false false false EN-US X-NONE X-NONE

Regards,

Sampath Kumar . G