Discussions

Expand all | Collapse all

Testing for Number of Weeks

  • 1.  Testing for Number of Weeks

    Silver
    Contributor
    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

    Top
    Contributor
    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

    Top
    Contributor
    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

    Silver
    Contributor
    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.