Basic "IF" not working

[Anonymous]

Hi, 

I want to use an IF statement to create categories for my data based in a number value.

I have a table with a column distance_covered (I checked and is number type). I want to create a new column that categorize the data in "Less than 5", "5 to 15", "15 to 25", "25 to 75" and "75 to 300".

The formula I'm using is: Distance Category = IF(bi_goto_session[distance_covered] > 0 && bi_goto_session[distance_covered] < 5, "Less than 5", IF(bi_goto_session[distance_covered] >= 5 && bi_goto_session[distance_covered] < 15, "5 to 15", IF( bi_goto_session[distance_covered] >= 15 && bi_goto_session[distance_covered] < 25, "15 to 25", IF(bi_goto_session[distance_covered] >= 25 && bi_goto_session[distance_covered] < 75 , "25 to 75", IF(bi_goto_session[distance_covered] >= 75 && bi_goto_session[distance_covered] < 300, "75 to 300")))))

But the results are:

Any ideas why??

Thanks, 

[Anonymous]

Maybe there is something wrong with the data type of your distance_covered column. I created a same table that has distance_covered's data type is Decimal number and it's working correctly with your IF formula

But I have some tip for you, instead of using many IF in a formula, we can use SWITCH, it will make your code look cleaner

Distance Category =
VAR distance = bi_goto_session[distance_covered]
RETURN SWITCH(TRUE(),
   distance > 0 && distance < 5, "Less than 5",
   distance >= 5 && distance < 15, "5 to 15",
   distance >= 15 && distance < 25, "15 to 25",
   distance >= 25 && distance < 75, "25 to 75",
   distance >= 75 && distance < 300, "75 to 300")

[Anonymous]

Hi, 

I did opened a new report, got the data "as new" and the same formula is working fine now...

Thanks for the help and for the tip!

[Anonymous]

Hi,

Can you let us know of the Data types of the Distance_Covered Column

Best Regards,
Vignesh M

If what I suggested worked for you feel free to Drop a "Kudos" and Consider to "Accept as Solution" if I solved your Issue 🙂

[Anonymous]

The data type and format of distance_covered is Decimal number.