Cognos Analytics

Hello,

I copied an expression from a working component but get a not valid error. Am I overlooking something?

Here is the full expression:

------------------------------
Christina Funk
------------------------------

Hi,

Why do you have a 'then' clause in the expression? You would only expect to see this as part of an if-then-else statement, but I'm not seeing an 'if' or an 'else'? If you really are using an if-then-else, you would need to use parentheses also

if (<something>)

then (<some expression>)

else (<some other expression>)

Overall, the expression doesn't seen to make sense. The first line evaluates to a true/false result so maybe it should be preceded by an 'if'? If that's true, you would need parentheses around it, plus an open parenthesis following the 'then', and a close parenthesis following the final 'end'. You would also need an 'else' (with an expression in parentheses).

Is this a subset of a larger expression?

Cheers!
MF.

------------------------------
Mark Fry
Technical Consultant
------------------------------

Original Message:
Sent: Thu April 25, 2024 04:16 PM
From: Christina Funk
Subject: Short Date to YYYY-MM Expression

Hello,

I copied an expression from a working component but get a not valid error. Am I overlooking something?

Here is the full expression:

------------------------------
Christina Funk
------------------------------

I think you've just missed copying "IF" from the first line of the expression, or possibly Case When, given that you've got an End on your line 19.

------------------------------
Chris Turner
------------------------------

Original Message:
Sent: Thu April 25, 2024 04:16 PM
From: Christina Funk
Subject: Short Date to YYYY-MM Expression

Hello,

I copied an expression from a working component but get a not valid error. Am I overlooking something?

Here is the full expression:

------------------------------
Christina Funk
------------------------------

It looks to me as if you simply want to extend the month to two digits if the renewal date was within the last twelve months. Depending on the backend database, you can also use LPAD. The function extends a string to a predefined length (e.g. in this case 2) with a predefined character (in your case "0").

Furthermore, your construct is missing brackets and it looks to me at least as if the case..when..then..end is not completely closed.

If ()
then 
  ( case 
       when xxxx then yyyy
       when xxxx then yyyy
       when xxxx then yyyy
       when xxxx then yyyy
 end
  )
else
(
)
        

------------------------------
Roger Lewin All for One SE
Senior Technical Consultant - Analytics
All for One SE
Dortmund
------------------------------

Original Message:
Sent: Thu April 25, 2024 04:16 PM
From: Christina Funk
Subject: Short Date to YYYY-MM Expression

Hello,

I copied an expression from a working component but get a not valid error. Am I overlooking something?

Here is the full expression:

------------------------------
Christina Funk
------------------------------

Does it have to be YYYY-MM or would YYYYMM also work? That's even easier ;)

cast(
year([Renewal Date])*100 + month([Renewal Date]
as varchar(6))

------------------------------
Roger Lewin All for One SE
Senior Technical Consultant - Analytics
All for One SE
Dortmund
------------------------------

Original Message:
Sent: Thu April 25, 2024 04:16 PM
From: Christina Funk
Subject: Short Date to YYYY-MM Expression

Hello,

I copied an expression from a working component but get a not valid error. Am I overlooking something?

Here is the full expression:

------------------------------
Christina Funk
------------------------------