if condition buckets not working

[surajap123]

Hi All,

i have below if() condition in the script. It is creating all output values except  '>20 days' value in the field final_date_bucket. seems >14 condition is eating up the >20 condition.

My requirement is to allow '>40 days bucket to allow holding starting with >20 days and similarly

>20 buckets to hold all values starting >20 days.

=if((today(1) - final_date)<5,'<5 days',
if((today(1) - final_date)>4 and (today(1) - 'date')<15,'5-15 days',
if((today(1) - final_date)>14,'>14 days',
if((today(1) - final_date)>20,'>20 days'))))              as final_date_bucket

Please help

[jwjackso]

All the IF tests between the first and last test need a min and max value test.

Instead of 

=if((today(1) - final_date)<5,'<5 days',
if((today(1) - final_date)>4 and (today(1) - 'date')<15,'5-15 days',
if((today(1) - final_date)>14,'>14 days',
if((today(1) - final_date)>20,'>20 days'))))              as final_date_bucket

Try

=if((today(1) - final_date)<5,'<5 days',
if((today(1) - final_date)>=5 and (today(1) - 'date')<14,'>=5 to <14 days',
if((today(1) - final_date)>=14 and (today(1) - final_date)<20,'>=14  to <20 days',
if((today(1) - final_date)>=20,'>=20 days'))))              as final_date_bucket

[MayilVahanan]

HI @surajap123 

Try like below

if((today(1) - final_date)>20,'>20 days',

if((today(1) - final_date)>14,'>14 days',

if((today(1) - final_date)>5 ,'5-14 days', <5 days',

)))             as final_date_bucket