Formula to Bucket Age of items MissMatch

[GMannella]

Hello,

I have run into a issue I cannot seem to trouble shoot (First day using Alteryx). I am creating a import/export tool and ran into a problem when creating a age bucket formula as I have done in my excel tool.

added_Formulas:

Field Name: Today

Purpose: (allows me to create an end date to age the overdue certifications)

Data Type: Date

Syntax: DateTimeToday()

Status: No Errors

Field Name: Days

Purpose: (similar to excel =(networkingdays), allows me to trim non workdays in age sum.)

Data Type: V_WString

Syntax: 1 + ((DateTimeDiff([Today],[POP End Date],"days")*5 -

(ToNumber(DateTimeFormat([POP End Date], "%w"))-

ToNumber(DateTimeFormat([Today], "%w")))*2) / 7) + 

IIF(DateTimeFormat([Today], "%w")=="6",-1,0) +
IIF(DateTimeFormat([POP End Date], "%w")=="0",-1,0)

Status: No Errors

Filed Name: Work Completion Cert Age:

Purpose: (if work completion certification date is blank, and there was a certification flag on the contract then the age == time from Period of Performance End Date to Today.)

Data Type: V_WString

Syntax: IF [Work Completion Certification Date]=Null() AND [Work Completion Certification Flag]!="N" THEN [Days] ELSE Null() ENDIF

Status: No Errors

Filed Name: Work Completion Cert Age Bucket:

Purpose: (bucket the Work Completion Cert Age into manageable data sets for visualization purposes)

Data Type: V_WString

Syntax:

IF [Work Completion Cert Age] > "365" THEN ">1 Year"

ELSEIF [Work Completion Cert Age] >= "180" THEN "180 Days - 1 Year"
ELSEIF [Work Completion Cert Age] >= "120" THEN "120-180 Days"
ELSEIF [Work Completion Cert Age] >= "90" THEN "90-120 Days"
ELSEIF [Work Completion Cert Age] >= "60" THEN "60-90 Days"
ELSEIF [Work Completion Cert Age] >= "30" THEN "30-60 Days"

ELSEIF [Work Completion Cert Age] > "0" THEN "<30 Days"

ELSE Null()

ENDIF

Status: SUCKS!

Explained: I do not receive any errors, and for the most part the formula works, but it does not bucket the age of the Work Completion Cert Age correctly. I uploaded the GOOD and the BAD, it is very random, only a dozen or so are incorrect and I cannot seem to troubleshoot...………..maybe b/c it is my 8th hour using this tool, but after an entire day wasted and one keyboard broken I figured I would reach out for some help :).

Cheers!

-Gabe

[echuong1 · Accepted solution]

Great work so far!

For your conditional statement, you need to adjust the formula to take into consideration both the min and max values for each bucket. Also, considering you're comparing numbers, the values do not need to be in quotations. The quotation marks make the values strings. Work Completion Cert Age needs to be a numeric value, not a string. Change the datatype for this field to something like double. You also need an "else" statement as a catchall. The end should be endif. For example: 

IF [Work Completion Cert Age] > 365 THEN ">1 Year"

ELSEIF [Work Completion Cert Age] >= 180 and [Work Completion Cert Age] <365 THEN "180 Days - 1 Year"
ELSEIF [Work Completion Cert Age] >= 120 and [Work Completion Cert Age] <180  THEN "120-180 Days"
ELSEIF [Work Completion Cert Age] >= 90 and [Work Completion Cert Age] <120 THEN "90-120 Days"
ELSEIF [Work Completion Cert Age] >= 60 and [Work Completion Cert Age] <90 THEN "60-90 Days"
ELSEIF [Work Completion Cert Age] >= 30 and [Work Completion Cert Age] <60 THEN "30-60 Days"

ELSEIF [Work Completion Cert Age] > 0 and [Work Completion Cert Age] < 30 THEN "<30 Days"

else null () 

endif

[GMannella]

thank you so much! 

that pretty much did it, 

had to change the data type to Double on a couple, and put ToNumber() in the Days syntax, and changed the Work Completion Cert age bucket to V_String and it worked!

thanks again.

Cheers!

-Gabe