Updating script with specific dates

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • Answered

How would I rewrite the script to account for both [Program approved] and [Permanently Rejected] fields to be specific for dates below? 

June2016 - May2017=2016

June2017 – May2018=2017

June2018 – May2019=2018

My current script is working but I need to account for 2018:

If([Customer - Utility]="ABC Energy" and (Date(2017,5,31)>=[Program approved] or Date(2017,5,31)>=[Permanently Rejected]),"2016","2017")

Photo of Stephen Anguiano

Stephen Anguiano

  • 394 Points 250 badge 2x thumb

Posted 1 year ago

  • 0
  • 1
If(

[Customer - Utility]="ABC Energy" and (Date(2018,5,31)>=[Program approved] or Date(2018,5,31)>=[Permanently Rejected]), "2018",

[Customer - Utility]="ABC Energy" and (Date(2017,5,31)>=[Program approved] or Date(2017,5,31)>=[Permanently Rejected]), "2017", "2016")
Photo of Stephen Anguiano

Stephen Anguiano

  • 394 Points 250 badge 2x thumb
My requirements have changed and know not dependent on Customer. 

So will the following still work if I write :

If(Date(2017,12,31)>=[Program approved] or Date(2017,12,31)>=[Permanently Rejected],"2018",

Date(2016,12,31)>=[Program approved] or Date(2016,12,31)>=[Permanently Rejected],"2016","2017")

Just curious if [Program approved] is blank/null does the formula think it is greater than 12.31.2017?

Thanks.
That looks like it will work,  but I don't think that the condition will be true if the dates are null.  Hence it will probably calculate to 2017.

I suggest that you specifically test for null dates and decide what you want to output for that situations.

IF(
IsNull([Program approved]) and Isnull([Permanently Rejected]), "say something",

Date(2017,12,31)>=[Program approved] or Date(2017,12,31)>=[Permanently Rejected],"2018",

Date(2016,12,31)>=[Program approved] or Date(2016,12,31)>=[Permanently Rejected],"2016","2017")
Photo of Stephen Anguiano

Stephen Anguiano

  • 394 Points 250 badge 2x thumb
This is the logic I decided to use:

If(Date(2017,12,31)>=[Program approved] or Date(2017,12,31)>=[Permanently Rejected],"2018",
Date(2016,12,31)>=[Program approved] or Date(2016,12,31)>=[Permanently Rejected],"2016","2017")


But it is making everything "2018". I know I have about 400 records that should be "2018" currently.
I see a Dynamic Filter on a field called Program Year.  I don't know what that field represents in your data.  What is your question?
Photo of Stephen Anguiano

Stephen Anguiano

  • 394 Points 250 badge 2x thumb
Program Year is determined by either [Program approved] or [Permanently Rejected]. Do you see the Dynamic Filter it has "Blank" for a year? It is because [Program approved] or [Pernanently Rejected] is "Null".

Will this script below fix that "Blank" in my Dynamic Filter?

IF(not IsNull([Program approved]), ToText(Year([Program approved])),not IsNull([Permanently Rejected]), ToText(Year([Permanently Rejected]))),
IF(
IsNull([Program approved]) and Isnull([Permanently Rejected]), "2018")

Thanks
I would write i a bit differently, bit yes, tat shoiuld eliomiate blamnks

IF(

IsNull([Program approved]) and Isnull([Permanently Rejected]), "2018",

not IsNull([Program approved]), ToText(Year([Program approved])),

not IsNull([Permanently Rejected]), ToText(Year([Permanently Rejected])))
Photo of Stephen Anguiano

Stephen Anguiano

  • 394 Points 250 badge 2x thumb
This works perfectly!!! 

Thanks
Great to hear, .... Upward and onward ho!
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,204 Points 20k badge 2x thumb
I think this is what you want but I did not test it:

 var Date CriticalDate = Min([Program approved], [Permanently Rejected]);
 
 If($CriticalDate <= Date(2016,12,31), "2016",
    $CriticalDate <= Date(2017,12,31), "2017",
    $CriticalDate <= Date(2018,12,31), "2018",
    $CriticalDate <= Date(2019,12,31), "2019"
)