Need clarification on formula

Follow

NATASHA BOROVIKOVA

• December 01, 2020 17:39

I need this formula below to show not only blank values for lastbuildorship but also put Y if the lastbuildorship is older then 3 years old. So all 3 rules have to work together: GlobalQ=0, lastbuildorship is blank or older then 3 years to receive Y. Please advise whats wrong with my formula?

 

IF(AND(#GlobalQ==0; AND(ISBLANK(#Lastbuildorship); #Lastbuildorship<ADDTODATE(TODAY();"3y"))); "Y"; "N")

 

 

0

• 

  Konsta Danyliuk

  • December 01, 2020 17:58

  Hello, Natasha.
  In case you need to put several conditions into an IF statement, I would recommend use || (OR) and && (AND) signs.

  For eample IF( #A>0 && #B<2; Then; Else)

  I guess in your case the following formula should work.

  IF(#GlobalQ == 0 && ( ISBLANK(#Lastbuildorship) || #Lastbuildorship < ADDTODATE(TODAY();"3y") ); "Y"; "N")

   

  0

  Comment actions Permalink
• 

  NATASHA BOROVIKOVA

  • December 01, 2020 22:44

  Could not make it work for the requirement of older then 3 years ago and it worked with the addition of "-", like this below

   

  IF(#GlobalQ == 0 && ( ISBLANK(#Lastbuildorship) || #Lastbuildorship < ADDTODATE(TODAY();"-3y") ); "Y"; "N")

  0

  Comment actions Permalink
• 

  Konsta Danyliuk

  • December 02, 2020 12:32

  Hi, Hatasha.
  Do you get any particular error message when trying to apply this formula? What does it say if you hover over the column in error?
  
  In my lab both below formulas work fine (Col #A is int, Col #B is date).

  IF(#A == 0 && ( ISBLANK(#B) || #B < ADDTODATE(TODAY();"3y") ); "Y"; "N")

  IF(#A == 0 && ( ISBLANK(#B) || #B < ADDTODATE(TODAY();"-3y") ); "Y"; "N")

  What Datameer version do you use?

  0

  Comment actions Permalink
• 

  NATASHA BOROVIKOVA

  • December 07, 2020 21:29

  Hi Konsta,

   

  I dont get any error message at all. I just do the data check myself visually and it shows transactions dates that are very recent. I use 10.1.5 version.

   

  Thanks

  0

  Comment actions Permalink
• 

  Konsta Danyliuk

  • December 08, 2020 07:45
  • Edited

  Hi, Hatasha.
  
  Do you think you could provide screenshots of your #GlobalQ, #Lastbuildorship, and the Result columns and point to incorrect calculation results, so I could see input and output data?
  
  In case the data is sensitive, perhaps it is possible to create a mock-up that demonstrates the problem.

  0

  Comment actions Permalink

Please sign in to leave a comment.