Set Analysis - First Sorted Value inside the operator

[Ja123__]

Hi Everyone

I have made an example of what I am trying to do between 2 tables. Not sure what I'm doing wrong.

I am ultimately trying to retrieve the Txt, for the last available rule, per member. The tables were ordered in Load script.

The =FirstSortedValue(RuleID,-dteEffectiveDate) works fine and brings in the right ruleID.

Now to go a step further I want to bring in the text so I have tried using the relevant RuleID in the set analysis, with an aggregation and it doesn't seem to bring in the right text? I also changed the '-' and used Minstring etc but looks like it is something else I am missing here.

AGGR(
MaxString
(
{<RuleID = {"=FirstSortedValue(RuleID,-dteEffectiveDate)"}>}
Txt
)

,MemberID)

[Ja123__]

=MinString({<RuleID = {"=AGGR(FirstSortedValue(RuleID,-dteEffectiveDate),MemberID)"}>}Txt)
I also tried this, which consistently gives now the First Text, not the last.

[anushree1]

Could you provide the sample input and desired output for better understanding and working scenario

[Ja123__]

Hi Anushree1

I will attach a QS app file with the example.

What I am trying to achieve is:

Member	Rule	Txt
1	5	bal
2	13	ybndfv
3	20	advrabbd

Rule is the "latest" applicable rule for the member. That calc is working. Txt which I cannot get right is the Txt column for the last rule for the member. 

I hope that helps

[tresesco · Accepted solution]

May be simply:

=FirstSortedValue(Txt,-dteEffectiveDate)

[anushree1 · Accepted solution]

please check the attached

i have used similar exp : FirstSortedValue(Txt,-dteEffectiveDate)

[Ja123__]

Seems I over complicated it. Thank you very much.