Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Basic "IF" not working

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, 

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      The data type and format of distance_covered is Decimal number.