Firssortedvalue - Get Max Date by Dimension

[pamaxeed]

Hi,

I am trying to get the Max Date by ISIN and YearMonth by using an Expression.

For this requirement I want to use the Firstsortedvalue. But I can not make it work!

Using a calculated Dimension works fine with aggr functionality. Somebody can help?

Expression with FirstSortedValue:

• FirstSortedValue(num(EVAL_DATE_FROM), -aggr(Max(EVAL_DATE_FROM), ISIN, [EVAL_DATE_FROM.autoCalendar.YearMonth]))

Caluclated Dimension:

• aggr(Max(EVAL_DATE_FROM), ISIN, [EVAL_DATE_FROM.autoCalendar.YearMonth])

Regards,

Patric

[sunny_talwar]

Why don't you use this

Max(TOTAL <ISIN> EVAL_DATE_FROM)

[pamaxeed]

Yep! right other way to achieve it! Thanks!

[pamaxeed]

Hi Sunny,

I tried your implementation and the output is not 100% what I expect.

Can you give a look to the attached .qvw App.

The expected output should be based on the selection and should be calculated dynamically.

As shown in the screenshot (14.01 is missing for all other January records).

The output should be grouped by ISIN and DateTo and should reflect always the last available Date for the Month grouped by ISIN and DateTo.

Regards,

Patric

[sunny_talwar · Accepted solution]

Try adding a NODISTINCT here

Aggr(NODISTINCT Max(DateTo), ISIN, DateMonthEnd)

[pamaxeed]

Great!