Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
gerhardl
Creator II
Creator II

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

1 Solution

Accepted Solutions
gerhardl
Creator II
Creator II
Author

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

View solution in original post

14 Replies
Anonymous
Not applicable

Try to create a flag in your load script:

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

Change your expression to :

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

Regards,

Dinesh.

gerhardl
Creator II
Creator II
Author

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?

gauravkhare
Creator II
Creator II

Hi,

KIndly try inserting only single quotes like ''

gerhardl
Creator II
Creator II
Author

Hi Gaurav,

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

G

IAMDV
Luminary Alumni
Luminary Alumni

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

gerhardl
Creator II
Creator II
Author

Hi DV,

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

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

Thanks,

G

Not applicable

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





gerhardl
Creator II
Creator II
Author

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

Not applicable

Hi,

Check this below Expression helps you.

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

Untested expression.

- Sridhar