1 Reply Latest reply on Dec 7, 2018 10:30 AM by Hari Ankem

    Overlapping Ranges in if Statement

    Chaan Thomas

      I wasn't quite sure how to phrase this topic in order to do a proper search, but what I would like is overlapping date ranges as a dimension.

       

      For instance my formula currently is as follows

       

      if [date_diff] <= 7 then "0 - 7"

      elseif [date_diff] <= 14 then "8 - 14 Days"

      elseif [date_diff] <= 21 then "15 - 21 Days"

      elseif [date_diff] <= 28 then "22 - 28 Days"

      elseif [date_diff] <= 35 then "29 - 35 Days"

      elseif [date_diff] <= 42 then "36 - 42 Days"

      elseif [date_diff] >42 then "> 42 Days"

      else null

      end

       

      Instead of 0-7 and then 8-14 etc. I would like it to be 0-7, then 0-14, 0-21 etc.

       

      I am trying to show a running trend as part of a table. Is a way to accomplish this?

       

      Thanks.

        • 1. Re: Overlapping Ranges in if Statement
          Hari Ankem

          You will need to create multiple calculated fields to achieve the same.

           

          0 - 7 Days: IF [date_diff] <=7 THEN 1 ELSE 0 END

          0 - 14 Days: IF [date_diff] <=14 THEN 1 ELSE 0 END

          0 - 21 Days: IF [date_diff] <=21 THEN 1 ELSE 0 END

          0 - 28 Days: IF [date_diff] <=28 THEN 1 ELSE 0 END

          0 - 35 Days: IF [date_diff] <=35 THEN 1 ELSE 0 END

          0 - 42 Days: IF [date_diff] <=42 THEN 1 ELSE 0 END

          > 42 Days: IF [date_diff] >14 THEN 1 ELSE 0 END

           

          The use the SUM of each of the above measure values and measure names to build the chart as desired.