Formula to Bucket Age of items MissMatch
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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:
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"
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
Solved! Go to Solution.
- Labels:
- Developer Tools
- Workflow
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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:
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"
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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