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

# Overlapping Ranges in if Statement

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

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.