Hi All,
I writen calcualtion as given below but not working. Could you any one correct it.
If (where (Not ([XY]) Inlist(“A”,“B”) and Between(ABS[AA];[RS])>[DD] OR isNull[AA]
Rup_Rup (BOB member since 2011-06-29)
It is hard to understand.
What you trying to achieve? Just write the logic
Maria_2 (BOB member since 2007-01-17)
sheriey and All
my actual formala was like this
I’m planning to create variable in web I.
[XY] !=(“A”,“B”) and (Abs[AA]-[RS] >[DD] OR[AA] Is Null
If Not ([XY]) Inlist(“A”,“B”) And (Abs[AA]-[RS]>[DD] Or IsNull [AA] Then 1 Else 0… not working any body could corret
Rup_Rup (BOB member since 2011-06-29)
This is my understanding. You can modify according to your need
If (((Not ([XY] inList(“A”,“B”))) and (Abs[AA]-[RS] >[DD]) OR (IsNull([AA])));1;0)
OR
If (((Not ([XY] inList(“A”,“B”))) and (Abs[AA]-[RS] >[DD]) OR (Not(IsNull([AA]))));1;0)
Good Luck!
Maria_2 (BOB member since 2007-01-17)
Hi sheriey
Thank you for reply
If (((Not ([XY] inList(“A” , “B”))) and (Abs[AA]-[RS] >[DD]) OR (IsNull([AA])));1;0) Its getting same issue error
Rup_Rup (BOB member since 2011-06-29)
I cannot tested it.
Just play around with the parenthesis little bit. It should work!
Maria_2 (BOB member since 2007-01-17)
Whenever I have complicated formulas like this I find it’s best to break them out during debugging into it’s component parts.
If (((Not ([XY] inList(“A” , “B”))) and (Abs[AA]-[RS] >[DD]) OR (IsNull([AA])));1;0) Its getting same issue error
Becomes a set of variables…
(Syntax is not exact, I’m being lazy this morning 
)
Var1 = IF [xy] inlist(“A”,“B”) Then “True” else “False”
Var 2 = ABS([AA]-[RS]) (I always make calculations seperate from logic)
Var 3 = IF Var2 > [DD] Then “True” else “False”
Var 4 = IsNull([AA]) (I beleive that returns 0 or 1 accordingly already).
Then put them into 4 individual columns so you can see the values… ONE of them will most likely have an error and you can then debug the problem.
Once corrected, you can rejoin the parts into a single statement if needed.
JPetlev (BOB member since 2006-11-01)
Hi JPetlev,
Thank you detailed explanation and tips appricite your help.
I was tried as you mentioned Var1 is working fine. Thank you lot.
My objects values as give below
[AA] ---------- - [RS] ----------- [XY] -------- [DD]
1/2/2011 ----------- 2/2/211 --------- A ------ - 7
2/2/2011 --------- 3/3/2011 ------ - B ---------- 69
XXXXXX----------- 4/22/2011 ----------- C -----33
XXXX … 4/5/2011 … …D … 36
XXXXX … 5/12/2011 …C …55
in AA some time blank May be that case getting error. Could you please advice me.
A & B are not in the list they only checking difference rest of values
Rup_Rup (BOB member since 2011-06-29)
If you’re saying AA can sometimes be ‘null’ (that is different than blank, which is a text field and thus you wouldn’t be subtracting a number from it), then YES that is your issue.
Your trying to do:
ABS([AA]-[RS])
Which would equate to “ABS(NULL-)” and that’s going to throw an error.
The question you need to ask yourself is , if “AA” is null, do I treat that number as a zero, or is there some other issue with my data.
If you want to treat it as a zero, that’s easy…
Just wrap AA up in another variable:
var_AA_Fixed = IF(ISNull([AA]);0;[AA])And use that new variable in your ABS formula as:
ABS([var_AA_Fixed]-[RS]) That should fix that particular error.
If however you weren’t expecting Null values, then you need to re-examine your query and/or universe to figure out why you were given nulls instead of numerical values.
JPetlev (BOB member since 2006-11-01)
Jpetlev,
Thank you for your time and advice var_AA_Fixed working fine.
Where as come to ABS([var_AA_Fixed]-[RS]) not working)
Below Im attached sample values could you give idea.
Abs([AA]-[RS] not working
Rup_Rup (BOB member since 2011-06-29)
As some folks suggested, create multiple variables and then see how each variable is working.
I would start with this first
Var1 = If (IsNull([AA]) or IsNull([RS]));0;ABS([AA]-[RS])
Var2 = if (Var1 > DD);1;0
Also I see 0 in column AA of the attached sheet, what kind of a object is that AA. If it is a Date object then 0 should not be there . Please check the object data type and format
santhano 
(BOB member since 2004-10-12)
If the AA and RS objects are not Date objects (means their data type is not date) then you have convert them to Date first and then apply the Date diff. Something like this
Var1 = If (IsNull([AA]) or IsNull([RS]));0;ABS(To_Date([AA],‘mm/dd/yyyy’)-To_Date([RS],‘mm/dd/yyyy’))
Please check the To_Date function syntax, I could be wrong with format
santhano (BOB member since 2004-10-12)
@ Santhano - Heh that’s partly my fault. I didn’t think to ask him what type of object it was when I gave him my example above, which replaces nulls with 0.
@ Rup_Rup - If AA is a date, the easiest thing to do instead is just to replace the 0 with a valid date in my AA_Fixed example, I suggest something like ‘1-1-1900’ to indicate a ‘null date’. (Assuming that 1-1-1900 would never be a date that is possible in your data set) That’s typically what I do with Null dates.
However, you REALLY need to understand your data as you will get some very bad results if you’re not careful about how you handle formulas with that date in them.
In your case from what I can glimmer from the examples you’ve provided, you might even substitue the [RS] value instead of the dummy date.(assuming that RS is a date as well here), and therefore your ABS(AA vs RS) will always return 0, which is probably closer to what you want if the AA value is null.
Santhano’s variable would actually be easier to deal with IMO
Var1 = If (IsNull([AA]) or IsNull([RS]));0;ABS([AA]-[RS])
The only caveat being that you don’t want to use AA or RS in formulas elsewhere. If you do you’ll want to wrap each up in a _fixed type variable instead. Otherwise Santhano’s method is a bit cleaner. Both will result in the correct value.
Again, the root of the issue is your data. Understand what data ‘holes’ you have and how you want to handle them first… and THEN try to code for those rules.
JPetlev (BOB member since 2006-11-01)
Hi All, JpetLev and Santhano thank your time and effort replys
Sorry I would be let you all know abefore what kind of objects are with me.
here are objects qualifications as follows,
AA Date
RS Date
DD number
XY string
I was calculated daysbetween function instated of Abs
first …var1 working fine.
2 and 3 part is woking fine
=If([var Filter Previouly Planned Flag]=N) Then “aaaa”
finally working perfect.
=Count([var Filter Previouly Planned Flag]=“Y”)
Rup_Rup (BOB member since 2011-06-29)