First Sorted Value with Set Analysis Error

[Ja123__]

Hi All,

What is the correct way to script this as I do this quite often in my one model and am picking up errors.

I want to get the salary that is the last salary for the MemberID, with the dteEffectiveDate8 being less than or equal to vEndDate. (31/12/2018)

So what I have done is written the following:

FirstSortedValue ( numAnnualSalary , -dteEffectiveDate8 )

I want to restrict it using vEndDate but I get a null result for:

FirstSortedValue ( {<dteEffectiveDate8 = {"<=$(vEndDate)"}>} numAnnualSalary , -dteEffectiveDate8 )

Is there something I am doing wrong? My base tables are sorted ascending and I have attached two images.

Kind regards,

James

[marcus_sommer]

Try this:

FirstSortedValue ( {<dteEffectiveDate8 = {"<='$(vEndDate)'"}>} numAnnualSalary , -dteEffectiveDate8 )

- Marcus

[Ja123__]

Hi Marcus

Thanks for the response. I tried that and got a null again.

Still trying to see what's wrong but if I use a different field it works. Not sure what's spitting out the odd null result.

FirstSortedValue ( {<dteEffectiveDate8 = {"<=$(vEndDate)"}>} numAnnualSalary , - SalaryID )

[marcus_sommer · Accepted solution]

Using from $(vEndDate) without wrapping single-quotey and a content of 31/12/2018 results in 0.00128... because it's just a division-statement. With the single-quotes the result would be '31/12/2018' which will be interpreted as date if it matched with your set default formatting of dates - if not you would compare a date against a string.

You may adjust here the formatting or the call of the variable or maybe following the best practice to use only pure numeric values within all calculations or matches to avoid the potential trouble with the formatting-stuff.

- Marcus