Hi,

Status =If(Application Out Service Date<CurrentDate(),"Retired","Active")

I am using this If statement. However, irrespective what the "Application out Service Date" is, always "Active" is being returned. 

May you advise what wouldn't the If statement ever return "retired". Thank you for the inputs. 

Reagrds,

Bhawna

Hi Bhawna - maybe it's the type of your "Application Out Service Date"?

I believe that operators would only work properly if they're comparing numeric columns.

Have you tried changing the column type of "Application Out Service Date" to Date?

I would also check if the values in this column are standardized... If they are not, you can try using the DateFormat() function for this. (https://tbmcouncil.jiveon.com/docs/DOC-5004)

Hello Kyle,

Thank you replying. When I trying to to change the column type of "Application Out Service Date" to Date, I am getting the this error - 

Failed to save table transform because of an invalid date column format for Application Out Service Date. null. 

Would you know why would this happen.

Thanks and Regards,

Bhawna

Have you tried changing it to numeric instead or using the date format function?

also, can you post some sample values you’re seeing in this column?

Hello Kyle,

Yes tried and the values that show up are as

1/1/19

3/6/19, etc

Quite as expected and how i would like to see them.

Please share your thoughts. 

Regards,

Bhawna

Good Morning

I've created a column with the CurrentDate configured, then used this type

of formula and it's worked in the past.

Best,

Audrey

Hello Audrey,

May you share the detailed steps, if possible. Thank you.

Regards,

Bhawna

Hi, Bhawna.

I've had nothing but trouble with dates in Apptio myself, and none of the documentation ever worked for me. So, I came up with a solution of my own that seems to work (so far).

Basically, I never use the Date data type in Apptio. If a date is just for someone's info, it goes in as a String (or Text, or whatever Apptio calls it). You can still sort on a String type, so it won't hurt anything. If I need to do date comparisons, like you show above, then I do the following...

1. Create a new String field called CompareDate (or something to that effect).
2. Enter a formula into CompareDate such that the output is the date in yyyymmdd format. Notice that there are no spaces, hyphens, slashes, etc. in that format.
3. Write your comparison formula to use CompareDate instead of the actual date column.
4. Hide the CompareDate column in your reporting, so the users see only the actual dates.

String fields can be compared just as numeric fields can. The comparisons are done in alphabetical order with numbers coming before letters. So, 2 < 9 < a < f < ...

Dates are tricky; when I do a comparison, I usually convert it into something like this (from my Contractors data):

=If(Days(Last Hire Dt)-Days(CurrentDate())<0, "Not Active", "Active")

Here is more info on the Days function Days function 

Hello Robert,

This worked like a charm.  Many thanks for sharing the knowledge. 

Regards,

Bhawna

Hello,

It could also be that Apptio can be weird about column names and the CurrentDate function. Try one of the below options:

1. Status =If({Application Out Service Date}<CurrentDate("MM-DD-YY"),"Retired","Active")

2. Status =If({Application Out Service Date}<CurrentDate(),"Retired","Active")

3. Status =If(Application Out Service Date<CurrentDate("MM-DD-YY"),"Retired","Active")

4. Status =If({TableName.Application Out Service Date}<CurrentDate("MM-DD-YY"),"Retired","Active")

5. Status =If({TableName.Application Out Service Date}<CurrentDate(),"Retired","Active")

I've found that I sometimes need the brackets around the column name, as well as sometimes need to have the format of the date field in there for the formula to work properly. Good luck!

Christy

Hello Christy,

Thank you for such detailed response. I tried all the options but it still didnt work. 

However, I am saving this valuable information for future references. 

Many thanks !