An Expression for the Last Time Something Happened

[ThePeterK]

Hey Folks, I'm writing a report tracking the number days since the last unsuccessful attempt of a process.

I'm trying to report the most recent, non-'S' value in the status table, denoting an unsuccessful attempt. The data table lists each step in the process. The report is grouped by the process ID and datetime. This is a one-to-many relationship.

A use case: if it were Thursday and there was an unsuccessful attempt Tuesday as well as last Wednesday, yesterday & today's report would report Tuesday as the last unsuccessful date and this past Thursday-Monday would report the Wednesday's unsuccessful attempt. 

This worked for reporting the last failure only:

FirstSortedValue({<STATUS=- {'S'}>} total [START_DATETIME.autoCalendar.Date],-[START_DATETIME])

but when I put in a date condition into the set expression, I get all null values:

FirstSortedValue({$<STATUS=- {'S'},[START_DATETIME.autoCalendar.Date]={'<=([START_DATETIME.autoCalendar.Date])'}>} total [START_DATETIME.autoCalendar.Date],-[START_DATETIME])

How do I make this calculate the last non-'S' date on each row so that I can return the days elapsed since the status wasn't 'S' on each row?

[rwunderlich]

Do you want to show the last unsuccessful event at different points in time?  That is, are you using [START_DATETIME.autoCalendar.Date] as a chart dimension?

-Rob

[ThePeterK]

Both questions: Yes I am.

[rwunderlich]

Your set analysis won't work because set analysis is evaluated only once per chart, not row-by-row. I can't think of an expression to get what you want, perhaps someone else can. 

The solution I would explore would be to build an "as of" table in the script. (If you are no familiar with as of table, search this site).  The As Of table would link each date with the dates that came before it, but using a new name for the status date, perhaps "Status Date". Then your expression would be something like:

Max{<STATUS=- {'S'}>} [Status Date])

-Rob