I have a straight table by Customer that shows when the lead was identified (StartDate), when the last sales call was placed, etc. The sales manager would like to me add a new column - has a demonstration been completed since StartDate? A demonstration has a specific code, 'SDC' in our database. That database lists, for every activity the sales rep takes, the date it occured and the "Result Code", of which SDC is an example. Previously, I had been listing the last three activities and their results codes by using FirstSortedValue(ResultCode,OnDate,-1) (and then -2), -3). The problem is, there could be literally dozens of activities over the two month span the sales manager wants me to check.
I could brute force an answer with an enormously long "IF" statement (using abbreviations here):
If(FSV(RC,OD,-1)<>'SDC',if(FSV(RC,OD,-2)<>'SDC',if(FSV(RC,OD,-3)<>'SDC' etc., etc., ad nausuem, but that seems inelegant.
In a procedural language, it would be simple:
n=1
DemoDone=FALSE
While (FSV(OD,OD,-n) >= StartDate)
if(FSV(RC,OD,-n) = 'SDC' then DemoDone = TRUE
next n
Is there some way to do this with a chart expression that I'm not aware of? Should I look at building a macro to do it?
