Is there a way to simply this calculated column expression?

Is there a way to simply this calculated column expression?
 

case 
when ((
 case [KPI (RAG)*]
 when "R" then 1
 when "A" then 2
 when "G" then 3
end) - sum(case [KPI (RAG)*]
 when "R" then 1
 when "A" then 2
 when "G" then 3
end) OVER (intersect([ASSET],previous([QUARTER]))))<0 then "D"
when ((
 case [KPI (RAG)*]
 when "R" then 1
 when "A" then 2
 when "G" then 3
end) - sum(case [KPI (RAG)*]
 when "R" then 1
 when "A" then 2
 when "G" then 3
end) OVER (intersect([ASSET],previous([QUARTER]))))>0 then "U"
when ((
 case [KPI (RAG)*]
 when "R" then 1
 when "A" then 2
 when "G" then 3
end) - sum(case [KPI (RAG)*]
 when "R" then 1
 when "A" then 2
 when "G" then 3
end) OVER (intersect([ASSET],previous([QUARTER]))))=0 then "S"
end

 

I would like to declare a function or variable to avoid smelly code. Something like this:

 

def caseKPI() :
 case [KPI (RAG)*]
  when "R" then 1
  when "A" then 2
  when "G" then 3
 end

case 
 when (caseKPI() - sum(caseKPI())
  OVER (intersect([ASSET],previous([QUARTER]))))<0 then "D"
 when (caseKPI() - sum(caseKPI())
  OVER (intersect([ASSET],previous([QUARTER]))))>0 then "U"
 when (caseKPI() - sum(caseKPI())
  OVER (intersect([ASSET],previous([QUARTER]))))=0 then "S"
end

 

or even better:

def caseKPI() :
 case [KPI (RAG)*]
  when "R" then 1
  when "A" then 2
  when "G" then 3
 end

def trend() :
  caseKPI() - sum(caseKPI()) OVER (intersect([ASSET],previous([QUARTER])))
 
case 
 when trend()<0 then "D"
 when trend()>0 then "U"
 when trend()=0 then "S"
end

 

In other words, can we declare local variables or functions within the expression?

(2) Answers

Login