Skip to main content
Announcements
Do More with Qlik - Qlik Cloud Analytics Recap and Getting Started, June 19: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
pamaxeed
Partner - Creator III
Partner - Creator III

Firssortedvalue - Get Max Date by Dimension

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])

Expression.PNG

Regards,

Patric

1 Solution

Accepted Solutions
sunny_talwar

Try adding a NODISTINCT here

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


Capture.PNG

View solution in original post

5 Replies
sunny_talwar

Why don't you use this

Max(TOTAL <ISIN> EVAL_DATE_FROM)

pamaxeed
Partner - Creator III
Partner - Creator III
Author

Yep! right other way to achieve it! Thanks!

pamaxeed
Partner - Creator III
Partner - Creator III
Author

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.

StateOfArt.PNG

Regards,

Patric

sunny_talwar

Try adding a NODISTINCT here

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


Capture.PNG

pamaxeed
Partner - Creator III
Partner - Creator III
Author

Great!