CODEWITHSUNDEEP
casecase-whencognos-11
massi
massi

Reputation: 21

Case When function with multiple conditions in Cognos Analytics 11.1.x

I am using Cognos embedded in Watson Studio and attempted creating the following calculation with case statement (both month_3 and avg2m are measures). Cognos reports XQE-V5-0017 V5 syntax error found for data item 'calculation-new' of query 'validateQuery', invalid token "<" found after "case (( allin_shaped_csv.month_3 - avg2m ) / avg2m ) when ".

May you help fixing the syntax error?

case (( allin_shaped_csv.month_3 - avg2m ) / avg2m )
   when <-0.50 then -100;
   when <-0.20 then -50;
   when <0 then -20;
   when 0 then 0;
   when >0 then 20;
   when >0.50 then 50;
   when >0.99 then 100;
end case;

Upvotes: 0

Views: 5601

Answers (1)

massi
massi

Reputation: 21

it looks it works this way :

case 
   when (( allin_shaped_csv.month_3 - avg2m ) / avg2m )<-0.50 then -100
   when (( allin_shaped_csv.month_3 - avg2m ) / avg2m )<-0.20 then -50
   when (( allin_shaped_csv.month_3 - avg2m ) / avg2m )<0 then -20
   when (( allin_shaped_csv.month_3 - avg2m ) / avg2m )=0 then 0
   when (( allin_shaped_csv.month_3 - avg2m ) / avg2m )>0 then 20
   when (( allin_shaped_csv.month_3 - avg2m ) / avg2m )>0.50 then 50
   when (( allin_shaped_csv.month_3 - avg2m ) / avg2m )>0.99 then 100
   else 0
end

Upvotes: 1

Related Questions