Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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.
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?
Hi,
KIndly try inserting only single quotes like ''
Hi Gaurav,
I have tried both single and double quotes, with and without spaces between them - nothing seems to work...
G
Gerhardl - Have you tried using the Null() function within your second expression?
Hi DV,
No - please could you show me how? On this expression
Avg({$<[Trans Amount]={} >} [Days since last movement])
Thanks,
G
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
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
Hi,
Check this below Expression helps you.
Avg({<[Days since last movement] = {"=len([Trans Amount)=0"}>} [Days since last movement])
Untested expression.
- Sridhar