topic Re: Firssortedvalue - Get Max Date by Dimension in QlikView

Firssortedvalue - Get Max Date by Dimension

pamaxeed — Thu, 22 Feb 2018 12:29:57 GMT

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:

Regards,

Patric

sunny_talwar — Thu, 22 Feb 2018 12:46:25 GMT

Why don't you use this

Max(TOTAL <ISIN> EVAL_DATE_FROM)

pamaxeed — Thu, 22 Feb 2018 12:51:19 GMT

Yep! right other way to achieve it! Thanks!

pamaxeed — Mon, 26 Feb 2018 10:34:14 GMT

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 — Mon, 26 Feb 2018 12:16:46 GMT

Try adding a NODISTINCT here

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

pamaxeed — Mon, 26 Feb 2018 12:31:55 GMT

Great!