Discussions

View Only

Testing for Number of Weeks  QuickBaseCoach Dev./Training02-05-2018 05:12 _anom Diebolt)02-05-2018 05:20 _anom Diebolt)02-05-2018 05:18 • 1.  Testing for Number of Weeks

Posted 02-05-2018 05:00
I have a numeric field 'weeks' I have used to work out number of weeks between two dates.  Works fine. I am then using following statements in a formula text field to allocate certain text values.   but the result is not what I expect.

If(
[Project Type] = "New" and ToNumber([Weeks])>=55 and ToNumber([Weeks])<68, "Phase 1",
[Project Type] = "New" and ToNumber([Weeks])>=41 and ToNumber([Weeks])<=54, "Phase 2",
[Project Type] = "New" and ToNumber([Weeks])>=27 and ToNumber([Weeks])<=40, "Phase 3",
[Project Type] = "New" and ToNumber([Weeks])>=13 and ToNumber([Weeks])<=26, "Phase 4",
[Project Type] = "New" and ToNumber([Weeks])>=1 and ToNumber([Weeks])<=12, "Phase 5", "Phase 6")

• 2.  RE: Testing for Number of Weeks

Posted 02-05-2018 05:12
What�s the symptom of the problem.

Also if Weeks calculates to a number near you break point such as 54.5, your formula is not catching that.

• 3.  RE: Testing for Number of Weeks

Posted 02-05-2018 05:20
I missed Mark's point. Maybe update as follows:
var Number NumWeeks = ToNumber([Weeks]);
If([Project Type] = "New",
If(
55 <= \$NumWeeks and \$NumWeeks < 68, "Phase 1",
41 <= \$NumWeeks and \$NumWeeks < 55, "Phase 2",
27 <= \$NumWeeks and \$NumWeeks < 41, "Phase 3",
13 <= \$NumWeeks and \$NumWeeks < 27, "Phase 4",
1 <= \$NumWeeks and \$NumWeeks < 13, "Phase 5",
"Phase 6"
) )

• 4.  RE: Testing for Number of Weeks

Posted 02-05-2018 05:18
I would write it like this:
var Number NumWeeks = ToNumber([Weeks]);
If([Project Type] = "New",
If(
55 <= \$NumWeeks and \$NumWeeks < 68, "Phase 1",
41 <= \$NumWeeks and \$NumWeeks < 54, "Phase 2",
27 <= \$NumWeeks and \$NumWeeks < 40, "Phase 3",
13 <= \$NumWeeks and \$NumWeeks < 26, "Phase 4",
1 <= \$NumWeeks and \$NumWeeks < 12, "Phase 5",
"Phase 6"
)
)

Writing this formula in this fashion will allow you to scan and update the breakpoints easily as everything is in a natural order and "pointing" in the same direction.

But (1) you did not state what you expect and (2) you said [Weeks] was a Numeric field so I don't know why you were converting it to a number with ToNumber().

Also, I would consider what you want when \$NumWeeks was 68 or greater as your draft formula is going to default to "Phase 6" and I am not sure that is what you want..

• 5.  RE: Testing for Number of Weeks

Posted 02-05-2018 21:22
Thank you so much.  It works great.  I was expecting a categorisation based on the weeks test.  I created the week numbers as numeric field but to convert to these to weeks, I ToWeeks" in the week formula.  May be I should remove the ToNumber from the formula.