Forum Discussion

ArshadKhwaja's avatar
ArshadKhwaja
Qrew Commander
8 years ago

Testing for Number of Weeks

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") 
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    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.
    • _anomDiebolt_'s avatar
      _anomDiebolt_
      Qrew Elite
      8 years ago
      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"
        ) )
  • _anomDiebolt_'s avatar
    _anomDiebolt_
    Qrew Elite
    8 years ago
    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..
  • ArshadKhwaja's avatar
    ArshadKhwaja
    Qrew Commander
    8 years ago
    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.